0

In Excel I'm using the MATCH function on a column of vehicle registration numbers. The strange thing I am having is that for all but one registration number the MATCH function is working correctly. It doesn't seem to like the cell containing the vehicle registration number that doesn't match. I've checked the format of the cell and it's identical to the other cells on the vehicle registration column.

I thought it might be something to do with the vehicle registration itself (which is a string, not a number) so I tried putting in the string example and putting the formula =MATCH(L1,C:C) where column C contains the vehicle registrations (the cell I'm having trouble with now reading example) and l1 containing the string example and still a N/A. I'm completely confused by this.

Are there any suggestions as to why this is happening?

Ram
  • 3,092
  • 10
  • 40
  • 56
Matthew Brophy
  • 65
  • 1
  • 2
  • 9

1 Answers1

1

Add the 3rd parameter to the MATCH function. Zero makes it an exact-match search:

=MATCH(L1,C:C,0)

Here is why this works:

If you do not specify a zero for the 3rd (yet optional) parameter, MATCH defaults to a binary search style. It's very fast, but it requires the source data to be sorted. If not sorted, MATCH will return the wrong value. Exact-match tells MATCH to assume that the source data are NOT sorted, and it searches linearly from top to bottom until it finds the match or errors by not matching any value.

Excel Hero
  • 14,253
  • 4
  • 33
  • 40