Excel Vlookup Tutorial Part 5.1 - Vlookup #N/A Error
The Vlookup #N/A error is returned if the Vlookup function fails to find a match to the supplied lookup_value. The reason for this, depending on the value of the supplied [range_lookup] argument, is generally one of the following:
|if [range_lookup] = TRUE|
(or is omitted)
The #N/A arises because either:
- The smallest value in the left-hand column of the table_array is greater than the supplied lookup_value.
- The left column of the table_array is not in ascending order.
|if [range_lookup] = FALSE||-||The #N/A error arises because an exact match to the lookup_value is not present in the left-hand column of the table_array.|
Diagnosing the Problem
If you still do not understand why your Vlookup function is returning the #N/A error, it may help to work through the following steps to identify the problem.
Step No. 1
Check that your Vlookup function has the correct syntax. The #N/A error could arise if the lookup_value or table_array have been incorrectly defined.
Therefore you need to check the following:
- That the lookup_value is actually set to the value that you want to look up;
- That the table_array is correctly set to the range of values that you want to search and return values from. Make sure that the column of values to be searched is the first column of the table_array;
- If the [range_lookup] argument is set to TRUE (or is omitted) make sure that the first column of the table_array is in ascending order.
Step No. 2
The remaining two steps use the spreadsheet below as an example. In the spreadsheet, the user expects the value "1110004" in cell B1 to match the value "1110004" in cell E6.
If you are satisfied that the syntax of your Vlookup function is correct, check for true equality between the cells that you believe should match.
In the example above you would need to test if Excel considers the contents of cells B1 and E6 to be truly equal. This can be tested by typing the following formula into any free Excel cell:
This formula will evaluate to TRUE if Excel considers the contents of cells B1 and E6 to be truly equal. In this case you need to re-check that you have input your function correctly.
If the formula evaluates to FALSE, however, this tells you that the cause of your error is that Excel does not consider the contents of cells B1 and E6 to be truly equal.
Step No. 3
If the above test returns FALSE (i.e. the cells that you expect to match are not truly equal), you need to find out why this is. This is likely to be due to one of the following reasons:
Possible Reason No. 1
You may have unseen characters, such as spaces, at the start or end of either the lookup_value, or in the cells of your table_array. These characters cause the lookup_value and the 'matching' value in the table_array to be slightly different.
Double click on each of the cells and check the contents to see if there are any unseen characters at the start or end of the cells. If so, you will need to remove any additional characters from the cells. This can be done manually, but if you want to change several cells, it may be faster to use the Excel Trim function, as follows:
- Create a new column next to the one with the additional spaces in it.
- Use the Trim function in your new column, to get rid of extra spaces.
(e.g. if the original data is in cell E2, the function =TRIM(E2) removes any leading or trailing spaces).
- Copy the Trim function down the entire column of values.
- Copy the contents of the new column and paste the values only into the original column, using paste->special->values.
- Delete the column containing the Trim function.
Note that the Trim function removes trailing spaces from the start and end of a text string. However, if you have other unseen characters, you may need to carry out the above steps using the Excel Clean function instead of (or as well as) the Trim function.
Possible Reason No. 2
The contents of the cells that are being compared may have different data types. For example, the cell containing the lookup_value may be stored as a number, whereas the values in the table_array may be stored as text (even though they may look like numbers).
One of the easiest ways to quickly identify if a value in a cell is a text value is to use the Excel ISTEXT function. I.e. for the example above, to check if cell B1 is actually a text value, type the following into any available cell:
Then check the contents of cell E6 by typing the following into any available cell:
The ISTEXT function returns TRUE if the supplied cell contains a text value or FALSE otherwise. Therefore, if the two above formulas return different results, you know that the contents of cells B1 and E6 have different data types.
To solve this problem, you need to force both sets of data to have the same data type. For example, if you want both sets of values to be stored as text, you can convert both sets of data to text, using Excel's Text To Columns tool as follows:
- Use the mouse to select the cells you want to convert to text (this must be done one column at a time).
From the Data tab on the Excel ribbon, select the Text to Columns option.This will bring up the 'Text to Columns' dialog box.
- Within the 'Text to Columns' dialog box, make sure the Delimited option is selected and click next.
- Make sure all the delimiter options are unselected and then click next again.
- You should now be offered a selection of Column Data Formats. Select Text and click the Finish button.
The data in your selected cells should now be stored as text within Excel and so your function should be able to 'look up' the matching value.
Note that you could also have chosen to convert the contents of your cells to Excel's 'general' type (which should convert values that could be interpreted as numbers into numeric values). To do this, you would simply select the column data format General in the Text To Columns tool.
Further Vlookup troubleshooting tips are provided, in the form of a handy Vlookup Quick Reference Card, on the Microsoft Office website.