Why does Excel MATCH() not find a match?

excel excel-match
Charlie Carwile picture Charlie Carwile · Nov 2, 2013 · Viewed 84.6k times · Source

I have a table with some numbers stored as text (UPC codes, so I don't want to lose leading zeros). COUNTIF() recognizes matches just fine, but MATCH() doesn't work. Is there a reason why MATCH() can't handle numbers stored as text, or is this just a limitation I'll have to work around?

Answer

barry houdini picture barry houdini · Nov 2, 2013

Functions like MATCH, VLOOKUP and HLOOKUP need to match data type (number or text) whereas COUNTIF/SUMIF make no distinction. Are you using MATCH to find the position or just to establish whether the value exists in your data?

If you have a numeric lookup value you can convert to text in the formula by using &"", e.g.

=MATCH(A1&"",B:B,0)

....or if it's a text lookup value which needs to match with numbers

=MATCH(A1+0,B:B,0)

Related questions

If two cells match, return value from third

Here's a simple explanation of what I'm having trouble with. Column A: List of 2300 order numbers Column B: Email Address associated with an order number Column C: List of 100 specific order numbers that I need the email address for So, …

Read More
Comparing two columns, and returning a specific adjacent cell in Excel

I am using a combination of if, vlookup, match, iserror functions, and unfortunately I've not been able to find the right formula. Comparing two columns for matches is easy enough. the tough part has been returning a specific cell once …

Read More
Check if an excel cell exists on another worksheet in a column - and return the contents of a different column

What I want to do is to say if the contents of cell D3 (on current worksheet) exist in column A in the first worksheet (in my case entitled list). (and they always do exist somewhere). Return the contents of …

Read More