Index match in 2 columns and one row - help...

[Cantonalives]

Hi,
I am trying to perform a 3 way index match. Please see dummy data attached.


Basically I am looking to perform 2 matches on columns and one match on a row.

So I would index the data tab – C2:I16

You will note from the #N/A that one of the many formulas I have tried have failed…

Last one was as follows :

=INDEX(Data!C2:I16,MATCH(1,(Data!A2:A28=Summary!$A2)*(Data!B2:B28=Summary!$B2)*(Data!C1:I1=Summary!C$1),0))

Any guidance much appreciated….

 

[Jake Collins]

Dear Cantonalives

The following array formula should achieve your desired result and can be copied down and across.

=INDEX(Data!$C$2:$I$16,MATCH(1,(Data!$A$2:$A$28=Summary!$A2)*(Data!$B$2:$B$28=Summary!$B2),0),MATCH(TRUE,Data!$C$1:$I$1=Summary!C$1,0))

 

[bosco_yip]

Or this array formula (pressing SHIFT+CTRL+ENTER) in C2, copy across:

=INDEX(Data!$C$2:$I$16,MATCH($B2,IF(Data!$A$2:$A$16=$A2,Data!$B$2:$B$16),0),MATCH(C$1,Data!$C$1:$I$1,0))

Regards
Bosco

 

[Cantonalives]

Hi Jake,
Thanks for that but does not work when dragged down to other criteria?




-CL.

 

[Awesome Wally]

Try this in C2: =INDEX(Data!$A$1:$I$16,SUMPRODUCT(--(Data!$A$2:$A$16=Summary!$A2)*--(Data!$B$2:$B$16=Summary!$B2)*ROW(Data!$B$2:$B$16)),MATCH(Summary!C$1,Data!$A$1:$I$1,0))

Attached is an updated file.

 

[Somendra Misra]

Hi,

Just another formula in Summary C1 and copy across:

=SUMIFS(INDEX(Data!$C$2:$I$16,,MATCH(C$1,Data!$C$1:$I$1,0)),Data!$A$2:$A$16,Summary!$A2,Data!$B$2:$B$16,Summary!$B2)

Regards,