I'm trying to look up a value based on multiple criteria rows x columns. The sample data looks as follows:
<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
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