# Index Match Multiple Criteria

##### New Member
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?

### Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

#### BarryL

##### Well-known Member
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

##### Well-known Member
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

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

#### steve the fish

##### Well-known Member
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")

##### New Member
Thank you all - Much appreciated!

Replies
3
Views
162
Replies
4
Views
654
Replies
14
Views
535
Replies
5
Views
512
Replies
1
Views
360

1,190,808
Messages
5,983,031
Members
439,815
Latest member
yoswosz

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back