22

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?

Ram
  • 3,092
  • 10
  • 40
  • 56
Charlie Carwile
  • 837
  • 2
  • 7
  • 9

3 Answers3

36

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)

barry houdini
  • 45,615
  • 8
  • 63
  • 81
  • 11
    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)` – JustinJDavies Nov 02 '13 at 16:26
  • That makes perfect sense. Thanks! – Charlie Carwile Nov 02 '13 at 16:47
  • 1
    @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! – Learner Mar 17 '16 at 06:49
  • 3
    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. – miken32 May 04 '16 at 23:34
  • 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)'` – Signal15 Jun 27 '17 at 19:45
  • @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 Jul 30 '18 at 20:36
  • @JustinJDavies Likewise, `VALUE` returns an error if the value is not a number. Not reliable, either. – ivan_pozdeev Jul 30 '18 at 20:46
0

If you are looking for the word test for example in cell A2, type the following:

=MATCH(""&"test"&"",A2,0)

If this isn't working then try =Trim and =Clean to purify your column.

If =Trim and =Clean don't help, then just use the left 250 characters... (As the Match Formula may encounter a timeout/overflow after 250 characters.)

=Left(A2, 250)

Ahmedakhtar11
  • 1,076
  • 11
  • 7
-1

If you are using names to refer to the ranges, once you have fixed the datatypes also redefine any names which refer to those ranges.

gzh
  • 1