How to get vlookup dialog box?
To use the VLOOKUP function in Excel: On the Formulas tab, in the Function Library group, click the Insert Function command. In the Insert Function dialog box: Search on "VLOOKUP" or, in the Or select a category drop-down box, select Lookup & Reference .
Many people have heard about Excel’s VLOOKUP and HLOOKUP functions but have unsuccessfully used them. This Vlookup tutorial will solve any confusion you have about using either of these lookup functions and provide you with some tips to master these like a pro.
The VLookup and Hlookup function are fantastic time-savers. They are a perfect example of how Excel can “do the work for you”.
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 tables) and HLOOKUP (used for horizontally presented data tables). We will look at the VLOOKUP function which is shown below:
=VLOOKUP(lookup_value, table_array, col_index_number, [range_lookup])
In our first example, we are using an employee bonus schedule where we want Excel to look at the number of years each staff member has been employed, then go to our bonus schedule, which is laid out in a vertical format, and identify how much their bonus is based on their years of employment.
In this example I’m only looking at 6 staff members so technically I could manually look these up myself. Imagine though if I had 500 employees which I needed to perform this task for – it would be very time consuming if I did this manually. This is where the vlookup is going to save me ALOT of time.
To create an Excel vlookup function, follow these steps:
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!
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.
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 Excel 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:
