Using Index and Match with Multiple Criteria

[Iris Stein]

I am trying to create a table using index and match, but due to the nature of the table contents (repeats in the names of items), excel is having difficulties finding the right cell to display. I have attached a sample file and would appreciate any suggestions for overcoming this type of issue.

 

[Khalid NGO]

Hi Iris,

See if this works:
=INDEX($C$2:$F$29,MATCH($J$1&$J2,INDEX($A$2:$A$29&$B$2:$B$29,),0),MATCH(K$1,$C$1:$F$1,0))

or this with sumproduct:
=SUMPRODUCT(($C$2:$F$29)*($A$2:$A$29=$J$1)*($B$2:$B$29=$J2)*($C$1:$F$1=K$1))

copy down and across

Regards,

 

[bosco_yip]

Another 2 options :

=SUMIFS(INDEX($C$2:$F$29,0,MATCH(K$1,$C$1:$F$1)),$A$2:$A$29,$J$1,$B$2:$B$29,$J2)

or,

=-LOOKUP(1,-INDEX($C$2:$F$29,0,MATCH(K$1,$C$1:$F$1))/($A$2:$A$29=$J$1)/($B$2:$B$29=$J2))

Regards
Bosco

 

[Khalid NGO]

@bosco_yip
Nice use of INDEX (as a range) within SUMIFS, didn't used this before.

 

[bines53]

Hello friends ,

Another one,

=AGGREGATE(14,6,$C$2:$F$29/(($A$2:$A$29=$J$1)*($B$2:$B$29=$J2)*($C$1:$F$1=K$1)),1)

David

 

[Iris Stein]

Thanks so much!