Match() returning the wrong row?

[Karl]

The range that Match() is searching on includes both "5B" and "5b".

The Match() function is returning the row that "5B" is in when the value
"5b" is being passed to it.

Here's how it's coded:

=MATCH("5b",Deals!$A$1:$A$2897,0)


any ideas?

Interestingly enought, the values "4B" and "4b" don't cause any problems.

TIA.

 

[Frank Kabel]

Hi
this is expected as this function is not case sensitive.
You should have the same isue with 4b and 4B Try instead
the following array formula (entered with
CTRL+sHIFT+ENTER):
=MATCH(TRUE,EXACT("5b",Deals!$A$1:$A$2897),0)

 

[Karl]

Thanks Frank. You're right. There is a problem with 4b and 4B as well. I had
not noticed.

I'm not able to get your solution to work, and I don't see how it could.
Exact() returns true or false. You're using it as the range parameter to the
Match() function, aren't you? Also I don't want Match() to return a row that
contains TRUE.

Am I wrong?

Any other ideas?

Thanks,

 

[Frank Kabel]

Hi
have you entered this formula as array formula? enter it with
CTRL+SHIFT+ENTER. This formula returns the row_index for the first time
your lookup value matches EXACTLY with your lookup range