Failure to Look Up Values in Excel
One common Excel problem is that users get an unexpected error when they attempt to look up or match
a lookup_value within an array - Excel doesn't seem to be able to recognise or 'see' the matching value.
Possible Reason No. 1 :
If your lookup_value or the array you are searching resides in a cell or range of cells,
you may have unseen spaces at the start or end of one (or some) of these cells.
This creates the situation where the contents of the two cells you are comparing look
the same but additional spaces in one of the cells cause the cells to actually have slightly different content.
Check the contents of the cells that you believe should match.
If there are unseen additional spaces in one or both of the lists, remove these using the
- 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
- Copy the contents of the new column and paste these over the top of the original column,
- Delete the column containing the TRIM function
Possible Reason No. 2 :
If your lookup_value and/or the array you are searching are located in a cell or range of cells,
the contents of the cells that are being compared may have different data types.
For example, one of the cell's contents may be stored as a number by Excel, and the
value in the second cell may be stored as text even though it looks like a number.
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 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. Select Text and click the
The data in your selected cells should now be stored as text within Excel.
Note that you could have chosen to convert the contents of your cells to Excel's 'general'
type, by simply selecting the column data format General in the Text To Columns tool.