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

Step 1 - Check That a Match to the lookup_value Does Exist in the table_array

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 in the left-hand column of table_array.

Step 2 - Check for Leading or Trailing Spaces

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.

Fix to this Problem: Remove Leading & Trailing Spaces From the lookup_value and the table_array

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:

• 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
  (eg. if the original data is in cell A1, the function =TRIM(A1) removes any leading or trailing spaces)
• Copy the contents of the new column and paste these into the original column, using paste->special->values
• Delete the column containing the Trim function

Step 3 - Check the Data Types of the lookup_value and the table_array

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 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.

Displaying Data Type in Excel Cells

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':

• Use the mouse to select the cell(s) that you want to align the contents of
• Right click using the mouse, and select the option Format Cells...
• Select the Alignment tab in the window that pops up
• Under the Horizontal setting of the Text alignment option, select General
• Click OK

Fix to this Problem: Force Both Sets of Data to Have the Same Data Type

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:

• Use the mouse to select the cell(s) for which you want to convert the data type (this must not span more than one column)
• From the Data tab at the top of your Excel workbook, select the Text to Columns ... option
• 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. If you want to convert your data to text, select the Text option, or, if you want to convert your data to number, select the General option
• Click the Finish button

Return to the Vlookup Error Page