Failure to Look Up Values in Excel

Possible Reason No. 1

If your Lookup_Value or Array 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.

Solution:

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 TRIM function:

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
3. Copy the contents of the new column and paste these over the top of the original column, using paste→special→values
4. Delete the column containing the TRIM function

Possible Reason No. 2

If your Lookup_Value or Array resides 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, whereas the value in the second cell may be stored as text even though it looks like a number.

Solution:

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:

1. Use the mouse to select the cells you want to convert to text (this must not span more than one column)
2. From the Data tab at the top of your Excel workbook, select the Text to Columns ... option
3. Make sure the Delimited option is selected and click next
4. Make sure all the delimiter options are unselected and then click next again
5. 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.

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.