excelexcel-formulavlookupexcel-match

Trouble with Index Matching two rows and one column value

I am attempting to Index and Match and find the green value labeled in my table below based on the criteria in the yellow cells. Any idea how to go about this, here is my current formula: 

INDEX($A$2:$F$31, MATCH($H$3,$B$2:$B$31,0), MATCH($H$4, $C$2:$C$31,0))

It keeps returning the "Type" and not the "Cats" value I would like to have, 0.1518. Would Vlookup + Match be easier? Any help would be greatly appreciated.

enter image description here

Basically, I am trying to match two row variables (City and Type) with the column variable (cats) to get the value. However, if I use vlookup then maybe I can just say go to the cat column instead of matching it with another cell.

Solution

  • Try this formula:

    =INDEX(E2:E31,MATCH(1,INDEX((H3=B2:B31)*(H4=C2:C31),0,1),0))

    Based on the non-array version of the formula found here: https://exceljet.net/formula/index-and-match-with-multiple-criteria