excelexcel-formulacelllookupindex-match

Returning a value with multiple column criteria in Excel

I have a dataset that looks like the following...

enter image description here

And I'm looking to fill the table below using formulas... enter image description here

Outcome...

enter image description here

I've tried doing an index match match but cannot get it to work on searching two columns rather than a column and a row. What is the formula I'm looking for?

I should note that maxifs and minifs will not work here since I have more specific Member/Measure variables that don't have these functions available.

Solution

  • With TOCOL function. Copy this array formula in the result table top-left cell and copy right/down in the table.

    =INDEX($C$1:$F$20,TOCOL(IF((J$21=$B$1:$B$20)*($I22=$A$1:$A$20),ROW(C$1:F$20),1/0),3),MATCH(J$20,$C$1:$F$1,0))

    Without TOCOL

    =INDEX($C$1:$F$20,SUM(IF((J$21=$B$1:$B$20)*($I22=$A$1:$A$20),ROW(C$1:F$20))),MATCH(J$20,$C$1:$F$1,0))

    enter image description here