Need INDEX MATCH Help

[ijhoeq]

Hello,

I'm using INDEX MATCH combination to search through some data in cells A1:B1373. The user inputs what they want to search in cell C1 and the output is in cell D1. The formula in cell D1 searches through the data to find a match in the range A1:A1373. The formula in cell D1 is shown below,

=INDEX(A1:B1373,MATCH(C1,A1:A1373,0),2)

The data in range A1:A1373 is a set of three digit codes in each cell (e.g. 8A3, 0F6, 100, 000, 020, etc.). The formula in cell D1 works great when it is searching for codes that are numbers only (e.g. cell C1 = 000, or 100, or 020, etc.) but when searching for a code such as 8A3 or 2AF I get "#N/A" in cell D1.

I've tried inserting a new column and using the CLEAN function but this did not work. Can someone please help me figure out why this isn't working?

Thanks!

 

[steve the fish]

Match will work happily with text values. Id check to see if the cell you think contains the lookup value (C1) in the lookup array (A1:A1373) doesnt have trailing spaces or something so they arent equal.

 

[ijhoeq]

Thanks! I had to use the TRIM function instead of CLEAN. There were spaces I wasn't seeing.