Examples and videos show how to use a VLOOKUP formula to pull data from the master table. Troubleshoot VLOOKUP when things go wrong. For a more flexible lookup formula, use INDEX / MATCH instead of VLOOKUP.
You can watch the steps for creating this formula in the Product Price VLOOKUP video, show below. The written instructions are below the video.
It's a good idea to store each lookup table on a separate worksheet in the workbook. Then, as you add and delete rows in the lookup tables, you won't accidentally add or delete rows in any other table.
In this example, the lookup table is on a sheet named Products.
Lookup formulas can work vertically, looking for values down a column, or they can work horizontally, looking for values across a row. In this example, the information will be stored vertically, with values down a column, and later we'll use a VLookup formula to do a vertical lookup.
Note: To make it easier to refer to the table, you can name the range. There are instructions here: Naming a Range
The VLOOKUP function has four arguments:
Once you have created the lookup table, you can create other formulas in the workbook, and pull information from the product list. For example, to prepare an invoice, you can enter a product code, and formulas will get the product name or price from the product table.
In this example, the invoice is created on a sheet named Invoice. The VLOOKUP formula should find an exact match for the product code, and return the product name.
To create the VLOOKUP formula that calculates the product price, follow these steps:
In some situations, an approximate match is preferred, so several values will return the same result. For example, when grading student papers, all papers with a grade of 85 or over should receive an A grade.
To view the steps for creating this formula, please watch the VLOOKUP video shown below. The written instructions are below the video.
In this example, the lookup table is created on a sheet named Grades. To create the lookup table, enter the minimum score for each grade in column A. Enter the matching Grade in column B. Sort the Scores in Ascending order.
Cells A2:B6 were named GradeList.
The scores are entered on a sheet named Report Card, where a VLOOKUP formula calculates the grade.
In the screen shot below, the formula has been copied down to row 6, and the you can see the formula in cell C6.
You can use an IF formula with a VLookup formula, to return exact values if found, and an empty string if not found.
To see the steps for setting up the IF and VLOOKUP formula, you can watch this short video. The written instructions are below the video.
To hide errors by combining IF with VLOOKUP, follow these steps:
If the lookup table contains any blank cells, a VLOOKUP formula will
return a zero, instead of a blank cell. You can use nested IFs to
handle the #N/A results, and the empty cell results. For example:
Thanks to Chip Pearson for suggesting this formula.
In Excel 2007, a new function, IFERROR, was introduced. You could use an IFERROR formula with VLookup to check several tables for a value, and return the related information when found. In this example, three regions, West, East and Central, have order sheets. On each sheet is a named range -- OrdersW, OrdersE and OrdersC.
On a sheet named Orders, you can enter an Order ID, then use a VLOOKUP with IFERROR to check each named range, and view the information about the selected order.
IFERROR(VLOOKUP(B6,OrdersW,2,FALSE), IFERROR(VLOOKUP(B6,OrdersC,2,FALSE),"Not Found")))
This checks the OrdersE table and if an error is found, checks OrdersW table, then OrdersC. If the OrderID is not found in any of the three tables, the Not Found message is shown in the cell.
You can also check multiple tables in older versions of Excel, where IFERROR is not available, using a longer formula:
In some tables, there might not be unique values any column in the lookup table. For example, in the table shown below, Jacket is listed twice in column A. However, there is only one record for each jacket and size combination -- Jacket Medium in row 4 and Jacket Large in row 5.
If you need to find the price for a large jacket, a VLOOKUP based only on column A would return the price for the first jacket listed (Medium). You would be underpricing the jacket -- selling it for 60.00, instead of 65.00.
To create unique lookup values, you can insert a new column at the left side of the table, and use a formula to combine the product and size. In cell A2, the formula combines the value in B2 and the pipe character and the value in C2.
=B2 & "|" &C2
Copy that formula down to the last row of data, so each row has a unique value in column A.
Note: Instead of the pipe character, you could use another character that isn't included in your data.
Then, in a VLOOKUP formula, combine the product and size as the Lookup_value. In cell H1, the formula combines the value in F1 and the pipe character and the value in G1.
=VLOOKUP(F1 & "|" &G1,$A$2:$D$5,4,FALSE)
Note: The price is now in column 4, instead of column 3.
Your VLOOKUP formula may return an #N/A, even though the value you're looking for appears to be in the lookup table.
A common cause for this error is that one of the values is a number, and the other is text. For example, the lookup table may contain '123 (text), and the value to look up is 123 (a number).
If possible, convert the text to numbers, using one of the methods shown here: Convert Text to Numbers
If you can't convert the data, you can convert the lookup value within the VLOOKUP formula:
If the lookup table contains numbers, and the value to look up is text, use a formula similar to the following:
The double unary (--) converts text to a number, and will work correctly even if the lookup values are numbers.
If the lookup table contains text, and the value to look up is numeric, use a formula similar to the following:
=VLOOKUP(A7 & ""),Products!$A$2:$C$5,3,FALSE)
The TEXT function converts a number to text, and will work correctly even if the lookup values are text. In the first example, the & operator creates a text string from an unformatted number. In the second example, a number formatted with leading zeros (e.g. 00123) would match a text "number" with leading zeros.
To see the steps for fixing the VLOOKUP problem when the lookup table has text values, watch this short video tutorial.
Another potential cause for no matching value being found is a difference
in spaces. One of the values may contain leading spaces (or trailing,
or embedded spaces), and the other doesn't. To test the values, you
can use the LEN function, to check the length of each value.
For example: =LEN(A7) will return the number of characters in cell A7. It should be equal to the number of characters in the matching cell in the lookup table.
If possible, remove the unnecessary spaces, and the VLOOKUP formula
should work correctly. If you can't remove the spaces, use the TRIM
function in the VLOOKUP, to remove leading, trailing or duplicate
spaces. For example:
If TRIM function alone doesn't solve the problem, you can try adding the SUBSTITUTE function, to remove unwanted characters. There is an example on the Contextures blog: Clean Excel Data With TRIM and SUBSTITUTE
Another way to fix VLOOKUP problems is with the CLEAN function, which can remove some unwanted characters from the text. There is more information on the CLEAN function in this Contextures blog post: 30 Excel Functions in 30 Days: 29 - CLEAN
If you copied data from a web page, it may contain non-breaking space ( ) characters. David McRitchie has written a macro to remove them, along with other spaces characters -- http://www.mvps.org/dmcritchie/excel/join.htm#trimall
A VLOOKUP formula may return the correct results at first, but then shows incorrect results if the list of items is sorted. This can occur if the reference to the Lookup value includes a sheet name. For example:
NOTE: This problem can occur with other functions too, such as an INDEX/MATCH lookup formula.
This type of reference is created if you click on another sheet while building the formula. As soon as you do that, Excel adds the sheet name to any subsequent references in the formula.
In the screen shot above, Dress is in cell B9, and cell C9 shows the correct price of $30.
However, after sorting the products A-Z, the Dress moves up to cell B5, but the formula in cell C5 continues to refer to cell B9. Because of the sheet names in the references, Excel retains the original references, instead of keeping a reference to the current row. Cell C5 is showing the price for a Sweater, instead of a Dress.
To solve the problem, remove any unnecessary sheet names from the VLOOKUP cell references. Here is the revised formula for cell C5:
After the unnecessary sheet names are removed, the list can be safely sorted, and the correct results will show for each item.
1. Download zipped VLOOKUP sample 2010 / 2007 workbook
2. Download zipped VLOOKUP sample 2003 workbook
3. Download zipped VLOOKUP Sorting problem sample file
Last updated: April 27, 2016 8:35 AM