Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Comma explanation
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 |
#2
|
|||
|
|||
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 "George" wrote: 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 |
#3
|
|||
|
|||
This is because that last argument in MATCH (exact match or not) is not
required. "George" wrote in message ... 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 |
#4
|
|||
|
|||
MATCH has an optional third argument Match_type which goes after that comma.
HTH. Best wishes Harald "George" skrev i melding ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Comma delimited bank info | Excel Discussion (Misc queries) | |||
Comma delimited question | Excel Discussion (Misc queries) | |||
HELP - I need to change space delimited to comma? | Excel Discussion (Misc queries) | |||
Excel How do I create a comma delineated xls file to a comma delineated. | Excel Discussion (Misc queries) | |||
.CSV file format - comma or semi-colon? | Excel Worksheet Functions |