How to use VLOOKUP in Excel

The VLOOKUP function in Excel is one of the most widely used lookup functions, but it can sometimes be tricky to get it working. The VLOOKUP function is a fantastic time-saver as it is a perfect example of how Excel can “do the work for you”. The VLOOKUP function allows you to have Excel look at a value, go and find the value in a table of data and respond back with a corresponding related value.

This VLOOKUP tutorial will help take out any confusion you have about using the VLOOKUP function and provide you with some tips to master it like a pro.

VLOOKUP Example

Below are some great VLOOKUP examples to show you how you could use a VLOOKUP in your workbook:

• Look up a staff or student ID and respond back with related information such as DOB/Age, phone number, email address, class details etc.
• Look up a product code and respond back with the product name, weight, size, supplier details, cost price or RRP, stock information and so forth.
• Look up a number based score and respond back with the correct grade for that score.
• Anything where you want to find a value from a larger set of information (called a table) and return a corresponding value from within that table.

VLOOKUP vs HLOOKUP

Anytime you have a table of data where you need to lookup one value to find a corresponding value, you can use a lookup function. Excel provides a VLOOKUP and HLOOKUP option and which you use is determined by the format, or layout of your table data.

The VLOOKUP function is used when the table of data you are looking up from is presented in a “vertical” format. Column 1 must have the lookup values, column 2 and so on will have the addditional information you can return back.

The HLOOKUP is when your data is presented in a “horizontal” format. Both functions provide the same end result but provide the flexibility of having your data table in whichever format you prefer.

VLOOKUP Syntax

An important part of understanding any function in Excel is understanding the syntax. The syntax defines the way a function looks and what information needs to be entered.

Here is a look at the VLOOKUP Syntax:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

You will notice that the VLOOKUP syntax includes references to the lookup value, the table array and the column index number (col_index_num). So what do all those mean and what are you meant to add for this part of the Syntax?

Lookup_value	The lookup value is the cell reference containing the value you want to lookup a corresponding value from.
Table_array	The table_array is the location of the data containing the value you wish to lookup.
Col_index_num	The col_index_number is the column number within the table_array where the answer will be found. This is NOT the column letter.
Range_lookup	The range_lookup tells Excel which type of matching to use: find the closest match, or find an exact match.

How to create a VLOOKUP in Excel

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 table, which is laid out in a vertical format, and identify how much each employee will receive as a bonus 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 – it would be very time consuming if I did this manually. This is where the VLOOKUP is going to save ALOT of time.

To create an Excel VLOOKUP function, follow these steps:

1. Open Microsoft Excel.
2. 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.
3. If you do not have an existing file, create a sample file such as the one shown below:

4. From the sample above you can see our Bonus Schedule table is displayed in cells A17:B21.

TIP: In a real-world scenario I would recommend having your table of data, e.g. the Bonus Schedule, on a separate worksheet, separate from the data you are working with.

5. Place your cursor in cell E5.
6. We will use the Function Wizard to provide some guidance for new users.
7. Select the Formulas tab and click the Insert Function button:

8. The Insert Function dialog box will appear:

9. In the Search for a function box type the function name (in this example, type VLOOKUP) you are searching for, click Go.
10. Select VLOOKUP and click OK.
11. The Function Arguments dialog box will now appear:

12. Place your cursor in the Lookup_value field; within this field, we must select the cell which contains the value we want to “lookup”.
13. 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:

14. Click and select cell D5.
15. Now move your cursor to the table_array field.
16. You must now select the location of your table data, be sure to only select the cells which contain data, you do not need to include the headings.
17. Select cells A17:B21.
18. Because we will be using AutoFill to copy this function to other cells, place your cursor within the A17 reference and press F4 on the keyboard once to make the reference “Absolute”. Repeat for the B21 reference.
19. The Table_array should look as follows:

20. Move your cursor to the Col_index_num field.
21. You must now enter the numerical value of the column you wish to retrieve the match result from.
22. 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:

The Column Index Number is not related to which column the data is located in. E.g. column A, B or C. It is the position within the table_array area. If the table_array consists of 2, 6 or 20 columns of information, which column number within that area is the data being returned from? In both examples above the table_array only contains two (2) columns of information. The first column contains the lookup_value (years of employment) and the second column contains the bonus amount, therefore both will use the col_index_num of 2.

23. Enter the value 2 into the Col_index_num field.
24. 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.

NOTES: You will notice in our table_array that 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 the range_lookup field 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 the range_lookup field to tell Excel to find only an Exact match.

25. In the range_lookup field type False:

26. Click OK.
27. The answer $55.00 will be returned as the bonus amount for the first person in my example.
28. You can double-check the answer by looking up the bonus amount for 1 year of employment, which is $55.

AutoFill the function

There is no need to repeat the process to create the vlookup 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!

1. Select cell E5.
2. Place your cursor on the bottom right corner of the cell till the mouse cursor changes to a +
3. Hold down the left mouse button and drag down to the bottom of cell D9 and release the mouse button.
4. The function will now be copied down the column:

Why is my VLOOKUP returning #N/A?

The #N/A error for cell E8 , E10, E13 and E14 is in fact Excel telling you that there was not an exact match for those particular values. The value being looked up is 11 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?”.

Essentially if you receive an #N/A error, it means Excel couldn’t find an exact match for your lookup value. This is the most common VLOOKUP error, and it usually comes down to one of the following:

• No matching value exists in the table_array, so double check the value is actually there.
• Extra spaces in either the lookup value or the table data. This is a sneaky one because the cell looks correct but Excel sees the space as part of the text. You can wrap your lookup value in the TRIM() function to strip hidden spaces, e.g. =VLOOKUP(TRIM(D5),$A$17:$B$21,2,FALSE).
• Text vs number mismatch: if your lookup value is stored as text but the table contains numbers (or vice versa), Excel won’t match them even though they look identical. Check the cell alignment: numbers align right, text aligns left by default.
• range_lookup left blank or set to TRUE, meaning Excel is trying to find an approximate match on data that isn’t sorted, or the lookup value falls outside the table’s range. Set it to FALSE for an exact match (see the earlier example in this post).

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

1. 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:

2. If you do not want to have these additional years displayed, hide the rows and leave only the rows with value amounts displayed. You can see below that rows 18, 20, 22, 24, 26-29 are hidden on the worksheet:

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

FAQ’s

Can VLOOKUP look to the left?

No. VLOOKUP can only look up values in the first column of your table_array and return a result from a column to the right of it. If you need to look up a value and return a result from a column to the left, you’ll need INDEX/MATCH or the newer XLOOKUP function instead, both of which aren’t restricted by column position.

How do I use VLOOKUP across different sheets?

You can reference another sheet in your table_array by including the sheet name followed by an exclamation mark, e.g. =VLOOKUP(D5,Sheet2!A1:B10,2,FALSE). Everything else about the formula works exactly the same.

How do I use VLOOKUP across different workbooks?

The syntax is similar to referencing another sheet, but you also include the workbook name in square brackets, e.g. =VLOOKUP(D5,[BonusData.xlsx]Sheet1!A1:B10,2,FALSE). Note that both workbooks need to be open for this to work reliably, otherwise you may need to update the link manually.

What’s the difference between VLOOKUP, INDEX/MATCH, and XLOOKUP?

VLOOKUP is the simplest to learn but has some limitations. It can only look right, and it breaks if columns are inserted or deleted within the table_array. INDEX/MATCH is more flexible (it can look in any direction) but requires two functions combined together. XLOOKUP, available in Excel 365 and Excel 2021 onwards, is the newest option and combines the simplicity of VLOOKUP with the flexibility of INDEX/MATCH. It can look in either direction and handles missing values more gracefully. If you’re using a recent version of Excel, check out my XLOOKUP tutorial to compare.

Can VLOOKUP look up more than one criteria at once?

Not directly, VLOOKUP only accepts a single lookup_value. If you need to match on multiple criteria (e.g. both a name AND a date), you’d typically create a helper column that concatenates the values together, or switch to INDEX/MATCH with an array formula.

Is VLOOKUP case-sensitive?

No, VLOOKUP treats uppercase and lowercase text as identical, so “Smith” and “smith” will be treated as a match.

You’ve now completed a basic VLOOKUP in Excel and seen how powerful it can be for saving time and reducing manual errors. If you’re ready to take your Excel skills even further, check out some of my other tutorials. Learn how to use XLOOKUP, a more flexible alternative to VLOOKUP that handles leftward lookups and missing values with ease. Explore how COUNTIF & COUNTIFS can help you count data based on multiple criteria, or use CONCATENATE/CONCAT to combine text values from different cells. These functions, along with VLOOKUP, are essential tools for anyone working with spreadsheets.

For a detailed breakdown straight from Microsoft, check out their official VLOOKUP function documentation.

Have a question about using VLOOKUP or another Excel function? Add it in the comments below.