Index Match Multiple Criteria

[MaddyK]

I'm trying to look up a value based on multiple criteria rows x columns. The sample data looks as follows:

Level	Target	Quartile	Discipline 1	Discipline 2	Discipline 5	Discipline 6	Discipline 10
2	Base	LQ			138,600.00		136,600.00
2	Base	M			152,500.00		149,000.00
2	Base	UQ			185,000.00		194,500.00
2	Total	LQ			140,000.00		139,100.00
2	Total	M			157,500.00		164,600.00
2	Total	UQ			185,000.00		264,000.00
3	Base	LQ	127,700.00		118,600.00		93,500.00
3	Base	M	141,500.00	156,500.00	131,300.00		118,000.00
3	Base	UQ	145,000.00		144,600.00		156,100.00
3	Total	LQ	127,700.00		129,100.00		93,500.00
3	Total	M	141,500.00	156,500.00	138,000.00		123,400.00
3	Total	UQ	160,500.00		169,400.00		188,400.00

<colgroup><col><col><col><col span="5"></colgroup><tbody>
</tbody>

I want to look up the result for :
Level: 2
Target: Base
Quartile: LQ
Discipline: 5

I have tried the following with the result shown:

=index(A5:AA53,match(C2,A5:A53,0),match(E2,B5:B53,0),match(G2,C5:C53,0),match(I2,A5:AA5,0)) Result=You've entered too many arguments for this function
={INDEX(A4:AA52,MATCH(C2&E2&G2&I2,A4:A52&B4:B52&C4:C5&A4:AA4,0))} Result=#N/A
={INDEX($A$4:$AA$52,MATCH(I2,$A$4:$AA$4,0),MATCH(1,IF($A$4:$A$52=C2,IF($B$4:$B$52=E2,IF($C$4:$C$52=G2,1))),0))} Result=Total

What am I doing wrong?

Thank you for your help

 

[BarryL]

try the below array. modify it to fit your table.

{=INDEX(A1:H14,MATCH(1,(A:A=2)*(B:B="Base")*(C:C="LQ"),0),MATCH(5,2:2,0))}

 

[steve the fish]

Maybe:

=INDEX($A$2:$H$14,MATCH(1,INDEX(($A$2:$A$14=2)*($B$2:$B$14="Base")*($C$2:$C$14="LQ"),0),0),MATCH(5,$A$2:$H$2,0))

 

[PlusX EAI]

Your question gave me the impression that you are looking for sumifs rather then index-match ?

 

[steve the fish]

Same sort of thing with SUMIFS.

=SUMIFS(INDEX($D$2:$H$14,0,MATCH(5,$D$2:$H$2,0)),$A$2:$A$14,2,$B$2:$B$14,"Base",$C$2:$C$14,"LQ")

 

[MaddyK]

Thank you all - Much appreciated!