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 seewhichvalues in the M column match the value of D34, notwhether all of them do(and then returns an array of trues & falses indicating which ones do). See Bandersnatch's answer. – k.. Jul 23 '17 at 4:09