How to Troubleshoot a VLOOKUP
VLOOKUP is a function in Microsoft Excel 2010 that allows you to specify a value, search for that value in the first column of a data table and then return a value on the same row, but in another column in that data table. VLOOKUP has a few particular rules about each of the function's arguments, along with various rules about the positioning of the data table, so if your VLOOKUP function is not working properly, you have to examine a number of things to try to find the problem.
Other People Are Reading
Instructions
-
-
1
Click on the cell that holds you VLOOKUP formula. Write the formula down so that you will be able to check the values in the formula while you look at your data table.
-
2
Ensure that the formula starts with an equals sign. "=VLOOKUP(..." is the correct way to start the formula, while "VLOOKUP(..." will return an error.
-
-
3
Observe the first value after the open parenthesis. This is the value that VLOOKUP will search for in the first column of your data table. If the value is text-based, it needs to be enclosed in double quotes, while if it is a cell reference or a number, it should not be enclosed in quotes. Also check your data table to ensure that the column you are using to look up this value is the leftmost column of the data table. Finally, ensure there is a comma between this value and the next value.
-
4
Compare the second set of values after the open parenthesis with your data table. This set of values has two cell references, separated by a colon. The first reference is the upper left cell of the data table you are looking up the value on, and the second reference is the bottom right cell. Check these references against your data table to ensure that they are correct. Also, ensure that there is a comma after the second reference before the third VLOOKUP value.
-
5
Check the third value after the open parenthesis. This value is a number, which represents the number of the column where you will find the result of the formula. The first column in the data table is column one, with each subsequent column to the right of the first being one number greater. If this value is less than one, or greater than the number of columns in the data table, the formula will return an error. Ensure that there is a close parenthesis or a comma after this value, depending on whether your VLOOKUP formula is using the optional fourth value.
-
6
Make sure that the fourth value is "True" or "False." If the value is omitted, it is the same as if it were "True." When this value is true, then the column where you are searching for the first VLOOKUP value needs to be sorted in ascending order, and if VLOOKUP cannot find an exact match, it will find the next largest value. If this fourth value is "False," the lookup column doesn't need to be sorted, but the formula will return "#N/A" if it cannot find an exact match.
-
7
Look at the data in the first column of your data table to see if the information has any leading spaces or trailing spaces, as this could cause VLOOKUP to find incorrect values. Also, if you are using VLOOKUP to search for a number, ensure that the first column is not formatted as text. You can change the format by selecting the column, right-clicking it and choosing "Format Cells."
-
1
