MATCH FUNCTION using 1 as match type....please help

[PP3321]

I have the following formula:

=MATCH(1,{0,0,0,0},1)

This returns 4.

This is very mysterious to me.

 

[PP3321]

My question is,
Does match function return the last value, if not find the exact match?

 

[vletm]

Syntax: MATCH(lookup_value,lookup_array,match_type)
If match_type is 1, MATCH finds the largest value that is less than or equal to lookup_value. The argument lookup_array must be placed in ascending order: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE.

 

[PP3321]

When I did the simple formula.
=INDEX(B2:B4,MATCH(A6,A2:A4,1))

So if it finds equal value, then first occurance.

But not equal value, then the last occurance....

I think I got confused there...

 

[shrivallabha]

Approximate Match option will always return results on binary search. MATCH is not the only function which lets you do approximate match e.g. VLOOKUP also has this option.

See here:
http://forum.chandoo.org/threads/how-vlookup-works.18378/

 

[vletm]

@PP3321=INDEX(B2:B4,MATCH(A6,A2:A4,1))
... not good to use match_type 1,
You should use 0 for match_type.

• If match_type is 1, The argument lookup_array must be placed in ascending order.
• If match_type is 0, The argument lookup_array can be in any order.
• If match_type is -1, The argument lookup_array must be placed in descending order.

 

[PP3321]

@shrivallabha
Thank you. I have read some but I need to spend more time to understand it.
Will get back to you.
@vletm
Thank you...