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