Comma explanation

  • Thread starter Thread starter George
  • Start date Start date
G

George

I'm curious to know and wondering if somebody could please
explain why the comma near the end of this formula does
not return an 'entered to many arguments' type of error
message? The formula gives the same result in my work with
or without the , before the ))

INDEX($A$1:$F$1,MATCH(LARGE(A7:F7,2),A7:F7,))

TIA
 
It doesn't do anything in this case, either of these will return the same
result

=INDEX($A$1:$F$1,MATCH(LARGE(A7:F7,2),A7:F7,1))

=INDEX($A$1:$F$1,MATCH(LARGE(A7:F7,2),A7:F7,))

=INDEX($A$1:$F$1,MATCH(LARGE(A7:F7,2),A7:F7))

The reason is that if the match type is omitted it will default to 1,
however if there would not be a match it will return the wrong value unless
the list is in ascending order

If the match type is 0

=INDEX($A$1:$F$1,MATCH(LARGE(A7:F7,2),A7:F7,0))

it will look for the exact match or else throw an error

Since you both do large and match in the same list (must me an exact match)
the correct formula should be

=INDEX($A$1:$F$1,MATCH(LARGE(A7:F7,2),A7:F7,0))

There is also

=INDEX($A$1:$F$1,MATCH(LARGE(A7:F7,2),A7:F7,-1))

finds the smallest value that is greater than or equal to lookup value
list must be in descending order

Regards,

Peo Sjoblom



If
 
MATCH has an optional third argument Match_type which goes after that comma.

HTH. Best wishes Harald
 
Back
Top