The Vlookup #N/A error is returned if the Vlookup function fails to match the supplied lookup_value.
As the Vlookup #N/A error occurs when a match to the lookup_value is not found, the first step when analysing this error message is to check if a match actually exists.
I.e. check the following:
|if [range_lookup] = TRUE
(or is omitted)
|-||Check if the first value in the left column of the table_array is greater than the supplied lookup_value. If it is, the Vlookup will stop at this point and return the #N/A error (remember that in this case, the left hand column of the table array must be in ascending order).|
|if [range_lookup] = FALSE||-||Check that the lookup_value is contained within the left-hand column of table_array.|
It is possible that your Vlookup #N/A error is caused by unseen spaces at the start or end of either the lookup_value, or the value in the table_array. If the spaces are present in one, but not the other of the 'matching' values, this will cause your Vlookup to fail.
Therefore, it is advised that you check the lookup_value and the 'matching' value in the table_array, and delete any unnecessary leading or trailing spaces.
This problem can be fixed by removing any leading or trailing spaces from the lookup_value or the table_array. This can be done manually, but if your table_array is located in a range of spreadsheet cells, it may be faster to use the Trim function, as follows:
Another common reason why the Vlookup fails to match values is that the values being compared have different data types. For example, the lookup_value may be a numeric value, whereas the values in your table_array may be text, (even though they may look like numbers).
Therefore, you should check the lookup_value and the 'matching' value in the table_array, to check that they have the same data type.
The easiest way to check if values in an Excel spreadsheet are stored as numbers or text values is to set the horizontal alignment of the cell to 'General'. You can then see the data type at a glance, as numeric values will be aligned to the right of the cell and text values will be aligned to the left.
To set the horizontal alignment of the cell to 'General':
If your Vlookup #N/A error is caused by different data types, you can fix this problem by forcing both sets of data to have the same type.
If your data is in located in a spreadsheet, the data type can be changed using Excel's 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.