A common question I encounter both in online forums and in face-to-face training courses is how users can have Excel “lookup” an answer for them based on data in a table or cell. Many people would have heard about the VLOOKUP and HLOOKUP functions in Excel but I often find that those who have tried to use the lookup functions have encountered simple issues which have discouraged them from pursuing the use of this function.

The Lookup function is a fantastic time saver and is a great way of showcasing exactly how Microsoft Excel can “do the work for you”. For those who have not heard of the lookup function, this post will provide you with a great introduction to these two functions and how they can be used to save time.

An important aspect of understanding any function in Excel is the ability to identify the parameters or syntax required.

There are two types of lookup functions: VLOOKUP (used for vertically presented data) and HLOOKUP (used for horizontally presented data). We will now take a look at the VLOOKUP function which is shown below:

**=VLOOKUP(lookup_value, table_array, col_index_number, [range_lookup])**

Lookup_Value |
The lookup value is the cell reference containing the value you wish to lookup. |

Table_Array |
The table_array is the location of the data containing the answer you wish to lookup. |

Col_Index_Number |
The col_index_number is the column number within the table where the answer is being returned from. |

Range_Lookup |
The range_lookup tells Excel to find the closest match, rather than looking for exact matches. |

To create a lookup function, follow these steps:

- Open
**Microsoft Excel** - If you wish to use an existing file which contains a table of data suitable for this exercise, press
**Ctrl + F12**, the**Open**dialog box will appear allowing you to locate the file and click**Open** - If you do not have an existing file, create a sample file such as the one shown below:

- From the sample above you can see our
table is displayed in cells*Bonus Schedule***A14:B22**.

TIP:In a real-world scenario I would recommend having your table of data on a separate worksheet, separate from the data you wish to lookup.

- In this example we will use a
**Vlookup**to have Excel find the bonus amount each employee will receive based on their years of employment. - Place your cursor in cell
**D4** - To use the Vlookup function we will use the
**Function Wizard**to provide some guidance for new users - Select the
**Formulas**tab and click the**Insert Function**button OR click the**Insert Function**button located on the**Formula Bar**

- The
**Insert Function**dialog box will appear:

- In the
**Search for a function**box type the function name (in this example, type**vlookup**) you are searching for, or if you are unsure which function to use, type in a description of what you wish to achieve, then click**Go** - A list of functions which are applicable will be displayed

- By clicking on each function displayed you can read a description for each below the list
- If you are attempting to use a new function, click the
**Help on this function**link in the bottom left of the dialog box, this will give you a full overview of the function and how it can be used - Select
**Vlookup**from the list and click**OK** - The
**Function Arguments**dialog box will now appear:

- Place your cursor in the
**Lookup_value**field; within this field, we must select the cell which contains the value we want to “lookup” - This field has the
**Collapse dialog box**button on the right side which means that Excel will allow you to use the mouse and select any cell on the worksheet, if the dialog box is blocking access to the cell you need to click on, click the Collapse button otherwise, just leave the entire Function Arguments dialog open and select the correct cell

- Click and select cell
**C4** - Now move your cursor to the
**Table_array**field - You must now select the location of your table data, be sure to only select the cells which contain data, do not include the headings
- Select cells
**A14:B22** - Because we will be using AutoFill to copy this function to other cells, place your cursor within the
**A14**reference and press**F4**on the keyboard once to make the reference “**Absolute**”. Repeat for the**B22**reference. - The
**Table_array**should look as follows:

- Move your cursor to the
**Col_index_number**field - You must now enter the numerical value of the column you wish to retrieve the match result from
- In this example, we want to return the value of the bonus amount. It is important to use the value of the column within the table data and not the column number within Excel. See the examples below:

- Enter the value
**2**into the**Col_index_num**field - Now move your cursor to the
**range_lookup**field. This field determines if you want Excel to find you the closest match or an exact match.

Info:You will notice in ourtable_arraythat only the years where an employee will receive a bonus are listed. Year 2, 4, 6 and 8 are not listed in the table as employees do not receive a bonus in these years. If you leave therange_lookupfield empty, Excel will find the nearest match, meaning that employees who have been employed for 2, 4, 6 and 8 years will still receive a bonus when they should not. To avoid this issue you can use therange_lookupfield to tell Excel to find only an Exact match.

- In the
**range_lookup**field type**False** - Click
**OK** - The answer
**$200**will be returned as the bonus amount for the first person in my example - You can double check the answer by looking up the bonus amount for 9 years of employment, which is $200

## AutoFill the function

There is no need to repeat the process to create the lookup function again. Use the AutoFill feature to copy the formula to other cells. Because we used absolute cell referencing (the dollar $ symbol in the cell references for the bonus schedule table), we can AutoFill this and our answers will be accurate for all employees. It is worthwhile having a go at a few of the examples yourself so that you get more than one practice using the Vlookup function – remember practice makes perfect!

- Select cell
**D4** - Place your cursor on the bottom right corner of the cell till the mouse cursor changes to a
**+** - Hold down the left mouse button and drag down to the bottom of cell
**D9**and release the mouse button - The function will now be copied down the column

## What about the errors?

You spot them too? The #N/A error for cell D8 is in fact Excel telling you that there was not an exact match for those particular values. The value being looked up is 13 years of employment, our Bonus Schedule table only goes up to 9 years of employment, therefore, Excel has responded with an error message. A common question I hear is: “I don’t want errors on my spreadsheet, how can I make this look better?”.

Here is my tip to fix this and make your spreadsheet look better.

- Adjust the
**table_array**area to include ALL possible answers. In this example, add entries for years 2, 4, 6, 8, 10, 11, 12 etc. In the bonus amount simply enter $0

- If you do not wish to have these additional years displayed, hide the rows and leave only the rows with value amounts displayed. You can see below that rows 15, 17, 19, 21, 23-26 are hidden on the worksheet.

- You may need to adjust the
**table_array**cell references within the Vlookup in case it does not automatically acknowledge these new entries

## Using a Hlookup

As mentioned, there are two lookup functions. Vlookup and Hlookup. In the above example, we have utilised the Vlookup option as the **table_array** data is displayed in a vertical layout. The Hlookup function is identical however can be used where the **table_array** is displayed horizontally, as shown below:

As an example, the **Hlookup** function used in cell **D4** would look as shown below:

You have now completed a basic lookup function in Excel. I hope this gives you an introduction to the Lookup function and how it can provide time-saving benefits. Be sure to check out other great articles covering **how to use Concatenate to join text together**, and an **Introduction to Functions in Microsoft Excel**.

## Leave a Reply