Excel Formula: What, in plain English, is in this array in an INDEX(MATCH())?

Question

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.

Answer 1

Let's simplify this a bit and see if we can figure out what's going on. In the picture below I've set up D1, E1 and F1 to mimic D34, F34 and K34 in your formula, and filled them with values. For grins, I added two more rows of values below the first three cells. More about those later.

Then in columns H, I and J, I've added arrays equivalent to your columns M, P and Y. I shortened them a bit also, mainly because the debug trick below won't work if they are entire columns.

In G7, I've entered just the MATCH() portion of your formula above. As shown by the curly brackets enclosing it, it's an array formula. It's entered by typing CTRL-Shift Enter, and Excel adds the curly brackets. Once that's done, you can fill down two more rows. It's clear that the MATCH() function is coming up with some answers.

You're correct that multiplying logical values is the equivalent of the AND() operation, except it gives 1 and 0 as results instead of True and False. You can check this by typing =True*True or =True*False in a cell, for example.

You asked what could be in the rather strange array (yes, it IS an array). So let's look at what Excel thinks its value is.

Click inside one of the formulas and then click on the element of the formula whose value you want to see - in this case, it's "lookup_array". This highlights the lookup_array as shown in the next picture.

Now hit F9. This makes Excel show the value of the highlighted element.

And Excel tells us that the value of the highlighted part of the formula is the array {1;0;0;0;0}. If you do this for G8 and G9, you'll see the values are {0;0;0;0;1} and {0;0;0;1;0} respectively. So the MATCH() function finds the position of the 1 in these arrays, i.e. 1, 5 and 4.

You can think of it like this: The first part of the lookup_array checks where D1 equals H1:H5 - that part returns {1;1;0;0;0}. Then checking E1 in I1:I5 gives {1;0;1;0;0}. Checking F1 in J1:J5 gives {1;0;1;0;0}. ANDing all three of these gives {1;0;0;0;0} which is the result that Excel shows for the whole (admittedly strange) array. Similarly, the formula in G8 finds {0;0;0;0;1}, {0;1;0;0;1} and {0;0;0;0;1) then ANDs them to get {0;0;0;0;1}.

Your guess about what's happening is right on: the MATCH() finds the first row (Match type = 0) in H, I and J that has values matching the cells in D, E & F, and then INDEX() returns that position in column B.

You can fiddle with the values above and check this out for yourself. Remember to use CTRL-Shift Enter to enter the array formula - you'll be doing that a lot since Excel has a bug/feature where clicking in the formula and then hitting return or tab gives the #VALUE! error. CTRL-Z will undo that.

Hope this helps and good luck.

Answer 2

Is this written as an array function? I.e., curly braces on either side and entered using ctrl/enter? If so, then the mid-section is comparing the value in the array corresponding to the row it's on. Given the multiplication I'd say you're right that it's looking for something where all 3 values are true.

I suspect, without seeing your spreadsheet (and admittedly without making it through the exhaustive details of your post) that there's a range that has this function, and you happen to show us one of them. I suspect the row before and the row after (and many others as well) have the same formula, with the row numbers adjusting accordingly. If so, then I double down on the expectation that this is an array function, written once and applied to a series of cells, applied with ctrl/enter to make it iterate throughout the cells at runtime.

And if that's the case, editing the formula and using enter to save it (even if you didn't make any actual edits or changes) will change the behavior and therefore the results.

If none of this is making sense, feel free to send me the spreadsheet and I'll take a look.