MATCH Function giving odd results

  • Thread starter Thread starter Mat Richardson
  • Start date Start date
M

Mat Richardson

Hi experts.

I wonder if you can help. I'm running a MATCH function on some data
like this:-

a
b
c
d
d
e

When I try the function =MATCH("d",A1:A6) I get 5 as my result. Why?
Should it not be 4 as the first time "d" appears in the range is
fourth? I'm confused by it.

Hope somebody can explain.

Regards,

Matt
http://2toria.com
 
Check the value in A4. It may look like a single character, but it's not.

I bet you have some sort of white space in that cell -- either space characters
or HTML non-breaking space characters.

I'd retype the entry and see what happens.

You could try:

=len(a4)
to see if there's more than one character.
 
Hi Dave.

It is a single character. I've just tried the example Ive given in a
fresh workbook, typing the letters in column A and nothing more and it
still returns 5 instead of 4.

Really confused...

Matt
 
The default return type for MATCH is 1, which finds the *largest* value that
is less than or equal to lookup value and of course the list must be sorted
for this to work correctly. So, what MATCH actually does in this case is
look for the *last* value that matches.

If you want a result of 4, in this case, you would need to use 0 as the
return type:

=MATCH("d",A1:A6,0)


HTH
Steve D.
 
Hi

You have to add a 0 as last argument to the function to find the first value
identical with the lookup value. Look in help for further explanation:

=MATCH("d",A1:A6,0)

Regards,
Per
 
Yep. I didn't notice the lack of that 3rd parm.

Sorry for leading you the wrong direction.
 
Back
Top