Excel Vlookup Tutorial Part 5.1 - Vlookup #N/A Error

Step 1 - Check The Syntax of Your Formula

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:

1. That the lookup_value is actually set to the value that you want to look up;
2. 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;
3. 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 2 - Check Data Types

If you are satisfied that the syntax of your Vlookup function is correct, check that the lookup_value has the same data type as the values to be searched (i.e. the values in the first column of the table_array).

If these values have different data types, Excel will be unable to compare them. For example, if the lookup_value is the text string "1110004", the Excel Vlookup function will not be able to find this value within the set of numeric values, 1110001, 1110002, 1110003, 1110004, etc.

This is shown in the spreadsheet below:

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.

Solution: Force the Data to Have the Same Type

If you find that your lookup_value and the first column of your table_array do have different data types, this can be resolved by changing one of the data types (for example, you might want to change the contents of column E of the above spreadsheet into text values).

For an explanation of how do this, see the Convert Number to Text or the Convert Text to Number pages.

Step 3 - Check For True Equality Between 'Matching' Values

If you are using the exact match version of the Vlookup function (i.e. with the [range_lookup] set to FALSE), it may be the case that the lookup_value is not exactly equal to the value that you believe it should match, within the table_array.

For example, in the spreadsheet below, the contents of cells B1 and E6 look equal, but Excel may not actually evaluate these two cells to be exactly equal.

In the example above, to test if Excel considers the contents of cells B1 and E6 to be truly equal, enter 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 or FALSE otherwise.

If the above formula evaluates to FALSE, 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. This is likely to be due to one of the following reasons:

Possible Reason No. 1 - Different Data Types

The lookup_value and the data in the first column of your table_array may have different Excel data types (see Step 2 above).

Possible Reason No. 2 - Unseen Characters

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.

Solution - Remove Unseen Characters

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:

1. Create a new column next to the one with the additional spaces in it.
2. 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).
3. Copy the Trim function down the entire column of values.
4. Copy the contents of the new column and paste the values only into the original column, using paste->special->values.
5. 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.