Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index / Match
Hi - I got this function example from the link below, but don't quite
understand the "1" as the match lookup value. Could some help? =INDEX($A$2:$A$7,MATCH(1,(A10=$B$2:$B$7)*(B10=$C$2 :$C$7),0)) http://www.contextures.com/xlFunctio...ml#IndexMatch4 Thanks. tinman |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index / Match
I believe the 1 is the value to be matched:
http://www.techonthenet.com/excel/formulas/match.php Dave -- Brevity is the soul of wit. "tinman" wrote: Hi - I got this function example from the link below, but don't quite understand the "1" as the match lookup value. Could some help? =INDEX($A$2:$A$7,MATCH(1,(A10=$B$2:$B$7)*(B10=$C$2 :$C$7),0)) http://www.contextures.com/xlFunctio...ml#IndexMatch4 Thanks. tinman |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index / Match
When these arrays are multiplied together:
(A10=$B$2:$B$7)*(B10=$C$2:$C$7) It will return an array of 1 or 0. Like this: (A10=B2)*(B10=C2) = 0 (A10=B3)*(B10=C3) = 0 (A10=B4)*(B10=C4) = 0 (A10=B5)*(B10=C5) = 1 (A10=B6)*(B10=C6) = 0 (A10=B7)*(B10=C7) = 0 Since the lookup value is 1 a match is found at the 4th position so the result of the formula is the value in cell A5 which is the 4th cell in the indexed range. Biff "tinman" wrote in message ... Hi - I got this function example from the link below, but don't quite understand the "1" as the match lookup value. Could some help? =INDEX($A$2:$A$7,MATCH(1,(A10=$B$2:$B$7)*(B10=$C$2 :$C$7),0)) http://www.contextures.com/xlFunctio...ml#IndexMatch4 Thanks. tinman |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index / Match
Thanks Biff...that makes perfect sense...
"Biff" wrote: When these arrays are multiplied together: (A10=$B$2:$B$7)*(B10=$C$2:$C$7) It will return an array of 1 or 0. Like this: (A10=B2)*(B10=C2) = 0 (A10=B3)*(B10=C3) = 0 (A10=B4)*(B10=C4) = 0 (A10=B5)*(B10=C5) = 1 (A10=B6)*(B10=C6) = 0 (A10=B7)*(B10=C7) = 0 Since the lookup value is 1 a match is found at the 4th position so the result of the formula is the value in cell A5 which is the 4th cell in the indexed range. Biff "tinman" wrote in message ... Hi - I got this function example from the link below, but don't quite understand the "1" as the match lookup value. Could some help? =INDEX($A$2:$A$7,MATCH(1,(A10=$B$2:$B$7)*(B10=$C$2 :$C$7),0)) http://www.contextures.com/xlFunctio...ml#IndexMatch4 Thanks. tinman |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index / Match
You're welcome. Thanks for the feedback!
Biff "tinman" wrote in message ... Thanks Biff...that makes perfect sense... "Biff" wrote: When these arrays are multiplied together: (A10=$B$2:$B$7)*(B10=$C$2:$C$7) It will return an array of 1 or 0. Like this: (A10=B2)*(B10=C2) = 0 (A10=B3)*(B10=C3) = 0 (A10=B4)*(B10=C4) = 0 (A10=B5)*(B10=C5) = 1 (A10=B6)*(B10=C6) = 0 (A10=B7)*(B10=C7) = 0 Since the lookup value is 1 a match is found at the 4th position so the result of the formula is the value in cell A5 which is the 4th cell in the indexed range. Biff "tinman" wrote in message ... Hi - I got this function example from the link below, but don't quite understand the "1" as the match lookup value. Could some help? =INDEX($A$2:$A$7,MATCH(1,(A10=$B$2:$B$7)*(B10=$C$2 :$C$7),0)) http://www.contextures.com/xlFunctio...ml#IndexMatch4 Thanks. tinman |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Index, Match and filters | Excel Discussion (Misc queries) | |||
Match Index | Excel Worksheet Functions | |||
Match or Index Question | Excel Worksheet Functions | |||
Match & Index | Excel Worksheet Functions | |||
Vlookup, Index & Match | Excel Worksheet Functions |