MATCH Function giving odd results

[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

 

[Dave Peterson]

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.

 

[Mat Richardson]

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

 

[Steve Dunn]

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.

 

[Per Jessen]

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

 

[Dave Peterson]

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

Sorry for leading you the wrong direction.