This topic describes the most common VLOOKUP reasons for an erroneous result on the function and provides suggestions for using INDEX and MATCH instead.
: Also, refer to the Quick Reference Card: VLOOKUP troubleshooting tips which presents the common reasons for #NA issues in a convenient PDF 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 constraint of VLOOKUP is that it can only look for values on the left-most column in the table array. 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 #N/A error results because the lookup value “Kale” appears in the second column (Produce) of the table_array argument A2:C10. In this case, 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 your columns. That may also be highly impracticable, 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. See the next section.
Consider using INDEX/MATCH instead
INDEX and MATCH are good options for many cases in which VLOOKUP does not meet your needs. The key 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—according to its position. MATCH returns the relative position of a value in a table/range. Use INDEX and MATCH together in a formula to look up a value in a table/array by specifying the relative position of the value in the table/array.
There are several benefits of using INDEX/MATCH instead of VLOOKUP:
-
With INDEX and MATCH, the return value need not be in the same column as the lookup column. This is different from VLOOKUP, in which 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 may not seem challenging, it can be cumbersome when you have a large table and have to count the number of columns. Also, if you add/remove a column 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 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—in which 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 offer the flexibility of making dynamic reference to the column which contains the return value. This means that you can add columns to your table without breaking INDEX and MATCH. On the other hand, VLOOKUP breaks if you need to add a column to the table—since it makes a static reference to the table.
-
INDEX and MATCH offers more flexibility with matches. INDEX and MATCH can find an exact match, or a value that is 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.
Syntax
To build syntax for INDEX/MATCH, you need to use the array/reference argument from the INDEX function and nest the MATCH syntax inside of it. This take the form:
=INDEX(array or reference, MATCH(lookup_value,lookup_array,[match_type])
Let’s use INDEX/MATCH to replace VLOOKUP from the example above. The syntax will look like this:
=INDEX(C2:C10,MATCH(B13,B2:B10,0))
In simple English it means:
=INDEX(return a value from C2:C10, that will MATCH(Kale, which is somewhere in the B2:B10 array, in which 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, take time to verify that the referenced cells don’t have hidden spaces or non-printing characters. Also, ensure that the cells follow the correct data type. For example, cells with numbers should be formatted as Number, and not Text.
Also, consider using either the CLEAN or TRIM function to clean up data in cells.
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.
Solution:
-
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—see the section above in this article. 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 is not sorted in the ascending (A-Z) order—you will see the #N/A error.
Solution:
-
Change the VLOOKUP function to look for an exact match. To do that, set the range_lookup argument to FALSE. No sorting is 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. (Excel stores time values as floating point numbers.) Excel cannot store numbers with very 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.
Need more help?
You can always ask an expert in the Excel Tech Community or get support in Communities.
