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

For example, if column A contains unwanted spaces, use the following steps to remove them:

  1. Create a new column next to column A.
  2. Use the TRIM function in your new column (column B), to get rid of extra spaces (eg. in cell B2 use the formula =TRIM(A2) and copy this down).
  3. Copy the contents of the new column (column B) and paste these over column A, using paste→special→values (i.e. paste the values, not the formulas into column A).
  4. Delete column B - the column containing the TRIM function.

Possible Reason No. 2 :

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:

  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. Deselect all of the delimiters (i.e. ensure none are selected) 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.

Return to the Excel Errors Page

Return to the Home Page