puzzled by Match function w/ no lookup_value

[makubexho]

hi everyone,

can somebody explain to me what this formula mean？

{=match(,{-4;#VALUE!;-8;#VALUE!;#VALUE;#VALUE!},1)}

the output of this formula is 3, i just can't get it, why not 1 but 3 since i thought -4 is the closest value to null (i.e. there is no lookup_value)?

second, what does it mean when lookup_value is omitted (like the formula above) when using Match function? what does Match look up in this case?

 

[Jonmo1]

It has to do with the binary search used by Match.
When the match type is 1, the array MUST be sorted Ascending.
Otherwise, you get all sorts of strange results.

 

[T. Valko]

hi everyone,

can somebody explain to me what this formula mean？

{=match(,{-4;#VALUE!;-8;#VALUE!;#VALUE;#VALUE!},1)}

the output of this formula is 3, i just can't get it, why not 1 but 3 since i thought -4 is the closest value to null (i.e. there is no lookup_value)?

second, what does it mean when lookup_value is omitted (like the formula above) when using Match function? what does Match look up in this case?

With the lookup_value argument being empty it's being evaluated as 0. Like this:

MATCH(0,{-4;#VALUE!;-8;#VALUE!;#VALUE!;#VALUE!},1)

The result is due to the fact that the match_type argument is set to 1 which requires the lookup_array to be sorted in ascending order which it is not. Since 0 is greater than an number in the lookup_array it "matches" the last number in the lookup_array, -8.

 

[Aladin Akyurek]

hi everyone,

can somebody explain to me what this formula mean？

{=match(,{-4;#VALUE!;-8;#VALUE!;#VALUE;#VALUE!},1)}

the output of this formula is 3, i just can't get it, why not 1 but 3 since i thought -4 is the closest value to null (i.e. there is no lookup_value)?

second, what does it mean when lookup_value is omitted (like the formula above) when using Match function? what does Match look up in this case?

MATCH's first argument which is omitted as 0 is read as 0. Since the
match-type is set here to 1, we get by binary search the position of
the last negative numeric value for 0 is bigger than the negative values
the array constant houses.

You can identify the last negative numeric value in this array constant
with e.g. LOOKUP...

=LOOKUP(,{-4;#VALUE!;-8;#VALUE!;#VALUE!;#VALUE!})

Here is a link that explains how binary search operates in Lookup, Match, etc.:

http://www.mrexcel.com/forum/showthread.php?t=310278 (post #7)