Index and match function using two values to return result

I found this formula somewhere and it works for what want. But i dont know why! I was wondering if someone could explain this formula and how it works. I am enclosing a file as well.

Hello, I assume the formula you want to understand is this one:

=INDEX($C$2:$C$19,MATCH(1,INDEX(($B$2:$B$19=J$15)*($A$2:$A$19=$I16),0),0))

Starting with the inner INDEX: $B$2:$B$19=J$15 will resolve to an array of TRUE and FALSE values, likewise $A$2:$A$19=$I16 will resolve to an array of TRUE and FALSE values.

When these two arrays are multiplied, the result is an array with only one 1 value. This array of values is fed to the MATCH() function, which looks up the exact position of the 1 in the array.

This position number is then fed to the outer INDEX, which returns the respective element from the range in column C.

Select J16 and inspect the formula with the Evaluate Formula tool on the Formulas ribbon.

The other formulas look longer, but they only use an error trap like

=if(iserror(<formula>),"",<formula>)

Since you are apparently using Excel 2007 or later, you can shorten this with the IFError function to

=IFERROR(<formula>,"")

or, applied to your formula:

=IFERROR(INDEX($C$2:$C$19,MATCH(1,INDEX(($B$2:$B$19=J$15)*($A$2:$A$19=$I16),0),0)),"")

cheers,

Within the Index function, the ranges are evaluated and result in arrays. If you just copy parts of it into a cell, that will not show the array.

Click the cell with the formula. Then use the Evaluate Formula tool to see how the formula resolves step by step.

If you would indicate your Excel version in your profile, I could tell you how to start the Evaluate Formula tool.

nested in the INDEX function, this part of the formula

($B$2:$B$19=J$39)*($A$2:$A$19=$I40)

will return two arrays. The first one is the result of "do the cells in B2 to B19 match the value in J39?". The answer is provided in an array that looks like

{FALSE,TRUE,FALSE,FALSE,FALSE,FALSE,FALSE, etc}

The second condition is "do the cells in A2 to A19 match the value in I40"? The answer is provided in an array that looks like

{FALSE,TRUE,FALSE,FALSE,FALSE,FALSE,FALSE, etc} or similar.

The two arrays are then multiplied with each other. When this multiplication happens, the TRUE will be translated as a 1 and the FALSE will be translated as a 0. So the first value of the first array (FALSE) will be multiplied with the first value of the second array (FALSE). The result (=0) will be stored in another array. Then the second value of the first array (TRUE) will be multiplied with the second value of the second array (TRUE) and the result will be stored as the second value ( = 1)of the new array, and so on. Each element of the first array will be multiplied with the respective element of the second array. The result is an array that looks like

{0,1,0,0,0,0, etc}

That is the array that the Match function looks at. The Match function will find the position of the 1 in that array. The result is 2, because the 1 is in the second position of that array.

The elements of that formula cannot be simply pasted into a cell. Arrays will not calculate correctly in a single cell unless you take certain steps to make them work. Within and INDEX function, a range of formulas and operations on that range will be treated as an array.