Why does Excel MATCH() not find a match?

Question

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?

Solution

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)

Comments

[JustinJDavies]

What barry says is correct but I would favour the more expressive =MATCH(TEXT(A1,"0"),B:B,0) or =MATCH(VALUE(A1),B:B,0)

[Charlie Carwile]

That makes perfect sense. Thanks!

[SIslam]

@barry Hey you are my GOD! Functions like MATCH, VLOOKUP and HLOOKUP need to match data type whereas COUNTIF/SUMIF make no distinction is oracle!

[miken32]

Note also that you can apply the transform to both sides, if you have no idea what the format is: =MATCH(VALUE(A1), VALUE(B:B), 0) works well for me.

[Signal15]

Using =MATCH(A1+0,B:B,0) helped me out. This works across tables as well, like =MATCH(My_Table[[#data],[Column_Name]]+0,[@[Value]],0)'

[ivan_pozdeev]

@JustinJDavies TEXT distorts the value (e.g. in your example, it'll print 1 if the value is 0.5; if I change the format to "0,0", it prints 1000,0 if the value is 1e3). So it's not a reliable conversion function -- while barry's method is reliable.

[ivan_pozdeev]

@JustinJDavies Likewise, VALUE returns an error if the value is not a number. Not reliable, either.