This topic describes the most common reasons as to why your VLOOKUP might be failing.
Tip: Refer to Quick Reference Card: VLOOKUP troubleshooting tips that describes the common reasons for #NA issues with VLOOKUP in a handy PDF (Portable Document Format) file. You can share the PDF with others or print for your own reference.
Problem: The lookup value is not in the first column in the table_array argument
One of the biggest limitations of VLOOKUP is that it can only look for values on the left-most column in the table array. So if your lookup value is not in the first column of the array, you will see the #N/A error.
In the following table, we want to retrieve the number of units sold for Kale.
The error is because the lookup value “Kale” is in the second column (Produce) in the table_array argument A2:C10, so Excel is looking for it in column A, not column B.
Solution: You can try to fix this by adjusting your VLOOKUP to reference the correct column. If that’s not possible, then try moving around your columns. That may be highly impractical if you have large or complex spreadsheets where cell values are results of other calculations, or maybe there are other logical reasons why you simply cannot move the columns around. The solution is to use a combination of INDEX and MATCH functions, which can look up a value in a column regardless of its location position in the lookup table.
Use INDEX/MATCH instead of VLOOKUP
INDEX/MATCH can be used when VLOOKUP does not meet your needs. The biggest advantage of INDEX/MATCH is that you can look up a value in a column in any location in the lookup table. INDEX returns a value from a specified table/range based on its position, and MATCH returns the relative position of a value in a table/range. Using INDEX and MATCH together in a formula, you can look up a value in a table/array by specifying the value’s relative position in the table/array.
There are several benefits of using INDEX/MATCH over VLOOKUP:
With INDEX and MATCH, the return value need not be in the same column as the lookup column, unlike VLOOKUP where the return value has to be in the specified range. How does this matter? With VLOOKUP, you have to know the column number that contains the return value. While this sounds like no big deal, it can be cumbersome when you have a large table and have to count the number of columns. Also, if you were to add/remove a in your table, you have to recount and update the col_index_num argument. With INDEX and MATCH, no counting is required as the lookup column is different from the column that has the return value.
With INDEX and MATCH, you can specify either a row or a column in an array or even specify both. This means you can look up values both vertically and horizontally.
INDEX and MATCH can be used to look up values in any column. Unlike VLOOKUP where you can only look up to a value in the first column in a table, INDEX and MATCH will work if your lookup value is in the first column, the last, or anywhere in between.
INDEX and MATCH offers the flexibility of making dynamic reference to the column which contains the return value. What this means is that you can add columns to your table and INDEX and MATCH will not break. On the other hand, VLOOKUP breaks if you had to add a column to the table as it makes a static reference to the table.
INDEX and MATCH offers more flexibility with matches. INDEX and MATCH can find an exact match, value greater, or lesser than the lookup value. VLOOKUP will only look for a closest match to a value (by default) or an exact value. VLOOKUP also assumes by default that the first column in the table array is sorted alphabetically, and suppose your table is not set up that way, VLOOKUP will return the first closest match in the table, which may not be the data you are looking for.
To build a syntax for INDEX/MATCH, you have to use the array/reference argument from the INDEX function and nest the MATCH syntax inside of it. So it looks something like:
=INDEX(array or reference, MATCH(lookup_value,lookup_array,[match_type])
Let’s use INDEX/MATCH to replace VLOOKUP in the above example. The syntax will look like this:
In simple English it means:
=INDEX(I want the return value from C2:C10, that will MATCH(Kale, which is somewhere in the B2:B10 array, where the return value is the first value corresponding to Kale))
The formula looks for the first value in C2:C10 that corresponds to Kale (in B7) and returns the value in C7 (100), which is the first value that matches Kale.
Problem: The exact match is not found
When the range_lookup argument is FALSE, and VLOOKUP is unable to find an exact match in your data, it returns the #N/A error.
Solution: If you are sure the relevant data exists in your spreadsheet and VLOOKUP is not catching it, make sure that the referenced cells don’t have hidden spaces or non-printing characters. Also make sure that the cells follow the correct data type. For example, cells with numbers should be formatted as Number, and not Text.
Problem: The lookup value is smaller than the smallest value in the array
If the range_lookup argument is set to TRUE, and the lookup value is smaller than the smallest value in the array, you will see the #N/A error. TRUE looks for an approximate match in the array and returns the closest value lesser than the lookup value.
In the following example, the lookup value is 100, but there are no values in the B2:C10 range that are lesser than 100; hence the error.
Correct the lookup value as necessary.
If you cannot change the lookup value and need greater flexibility with matching values, consider using INDEX/MATCH instead of VLOOKUP. With INDEX/MATCH, you can look up values greater than, lesser to, or equal to the lookup value. For more information on using INDEX/MATCH instead of VLOOKUP, refer to the previous section in this topic.
Problem: The lookup column is not sorted in the ascending order
If the range_lookup argument is set to TRUE, and one of your lookup columns are not sorted in the ascending (A-Z) order, you will see the #N/A error.
Change the VLOOKUP function to look for an exact match. To do that, set the range_lookup argument to FALSE. There is no sorting necessary for FALSE.
Use the INDEX/MATCH function to look up a value in an unsorted table.
Problem: The value is a large floating point number
If you have time values or large decimal numbers in cells, Excel returns the #N/A error because of floating point precision. Floating point numbers are numbers that follow after a decimal point. (Note that Excel stores time values as floating point numbers.) Excel cannot store numbers with large floating points, so for the function to work correctly, the floating point numbers will need to be rounded to 5 decimal places.
Solution: Shorten the numbers by rounding them up to five decimal places with the ROUND function.
Do you have a specific function question?
Help us improve Excel
Do you have suggestions about how we can improve the next version of Excel? If so, please check out the topics at Excel User Voice.