Failure to Look Up Values in Excel
Users of the VLOOKUP, HLOOKUP or MATCH functions sometimes get an unexpected #N/A error when they attempt to look up or match a lookup value within an array. This error generally indicates that the function has failed to find the lookup value within the lookup array.
But what if you can see that the matching value is present in the lookup array, but Excel still doesn't find this value?
For example, if a lookup function was used in the spreadsheet below, the user might expect the lookup value "1110004" in cell B1 to match the value "1110004" in cell E6.
If your function fails to find this match (signalled by the #N/A error), this may be because Excel does not consider the two values to be exactly equal. You can test for this problem via the following steps:
Step No. 1
First check for 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 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 the contents of cells B1 and E6 are not truly equal.
Step No. 2
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. The reason is likely to be one of the following:
Possible Reason No. 1
You may have unseen characters, such as spaces, at the start or end of either the value you are looking up, or in the cells of your lookup array. These characters cause the lookup value and the 'matching' member of the lookup array to be slightly different.
In this case, remove any additional characters from the cells.
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 lookup 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.
Force both sets of data to have the same type. For example, if you want both sets of values to be stored as text, convert both sets of data to text, using Excel's Text To Columns tool:
- 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 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. 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.