I'm trying to help a friend with an Excel INDEX(MATCH())
she inherited at work, but it's got me stumped. Can people help me better understand what's going on inside of it? Here's the format:
=INDEX(
'[Spreadsheet2.xlsx]TabOfInterest'!$B$B
,
MATCH(
1
,
(
(D34='[Spreadsheet2.xlsx]TabOfInterest'!$M:$M)
*
(F34='[Spreadsheet2.xlsx]TabOfInterest'!$P:$P)
*
(K34='[Spreadsheet2.xlsx]TabOfInterest'!$Y:$Y)
)
,
0
)
,
1
)
I can see that if the number "1" (standing in for "TRUE," it seems, here) appears anywhere in some sort of array of TRUEs/1s & FALSEs/0s that was built by AND-ing together other BOOLEAN-filled arrays (however that's supposed to work in Excel...), then INDEX will pick up "the row-number" of whatever-mess-that-was that resulted in "1" being present within, and INDEX will return the value of Spreadsheet 2's cell at the intersection of that row and column B.
But I have no idea what's going on in that 2nd parameter of MATCH.
I tried playing around with a blank spreadsheet and couldn't even deduce what =(SingleCell=RangeOfCells)
does -- or for that matter, what =RangeOfCells
does -- the return-value seems to be dependent on which cell the formula is typed into (its row offset from the data it's inspecting), and adding $
before row numbers in those formulas doesn't make a difference.
What fundamentals about referring to ranges, and doing "equality" comparisons between cells and ranges, do I need to know to better understand what's going on in this formula and, ultimately, what the INDEX(MATCH())
is trying to look up?
What on earth is in this array?
(D34='[Spreadsheet2.xlsx]TabOfInterest'!$M:$M)
*
(F34='[Spreadsheet2.xlsx]TabOfInterest'!$P:$P)
*
(K34='[Spreadsheet2.xlsx]TabOfInterest'!$Y:$Y)
Also, if I had to guess, I suppose I'd say that the overall function does this, but it's bothering me that I can't explain why:
If there's a single row of Spreadsheet2 where ALL three of these conditions are TRUE:
- cell D34 of whatever spreadsheet this INDEX-MATCH is inside of appears in Spreadsheet2's column "M" AND
- cell F34 of whatever spreadsheet this INDEX-MATCH is inside of appears in Spreadsheet2's column "P" AND
- cell K34 of whatever spreadsheet this INDEX-MATCH is inside of appears in Spreadsheet2's column "Y"
Then return the value of the cell in Spreadsheet2, Column B, whatever-row-of-Spreadsheet2-that-compound-condition-was-true-for.
D34='[Spreadsheet2.xlsx]TabOfInterest'!$M:$M
checks to see which values in the M column match the value of D34, not whether all of them do (and then returns an array of trues & falses indicating which ones do). See Bandersnatch's answer.