Using Multiple Criteria in Index Match Array formula

[dpkdileep]

Hi,

I was using an Index Match Array formula to get multiple results according to one criteria.

In this formula, I am able to get the list of names for location "XX". That is A, D, E.

{=INDEX($A$2:$A$6,SMALL(IF(ISNUMBER(MATCH($D$2:$D$6,Location,0)),MATCH(ROW($D$2:$D$6),ROW($D$2:$D$6)),""),ROWS($E$1:E1)))}

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Age[/TD]
[TD]Qualification[/TD]
[TD]Location[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]21[/TD]
[TD]AAA[/TD]
[TD]XX[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]21[/TD]
[TD]AAA[/TD]
[TD]YY[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]25[/TD]
[TD]BBB[/TD]
[TD]ZZ[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]21[/TD]
[TD]AAA[/TD]
[TD]XX[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]20[/TD]
[TD]BBB[/TD]
[TD]XX[/TD]
[/TR]
</tbody>[/TABLE]


Now, I have come across a scenario where I need to use two criteria. That is, Qualification and Location.
For example, I need the Names where the qualification is "AAA" and location is "XX".

Can someone help me in modifying the formula to get the above result.

Thanks in advance

Regards
Deepak

 

[AhoyNC]

If you have Excel 2010 or later try:
Copy formula down as needed.

Excel Workbook
	A	B	C	D
1	Name	Age	Qualification	Location
2	A	21	AAA	XX
3	B	21	AAA	YY
4	C	25	BBB	ZZ
5	D	21	AAA	XX
6	E	20	BBB	XX
7
8		Location	Qualification
9	Match	XX	AAA
10
11	Name
12	A
13	D
Sheet

 

[Fluff]

Hi & welcome to MrExcel.
How about

Book1
	A	B	C	D	E	F	G
1	Name	Age	Qualification	Location		XX	AAA
2	A	21	AAA	XX			A
3	B	21	AAA	YY			D
4	C	25	BBB	ZZ
5	D	21	AAA	XX
6	E	20	BBB	XX
Roster

Cell Formulas
Range	Formula
G2	=IFERROR(INDEX($A$2:$A$6,AGGREGATE(15,6,(ROW($A$2:$A$6)-ROW($A$2)+1)/(($C$2:$C$6=$G$1)*($D$2:$D$6=$F$1)),ROWS($A$1:$A1))),"")

 

[dpkdileep]

Thanks Fluff & AhoyNC.

I have run this with my data and it is perfectly working fine. Thanks for your support.

I have one more question on this.

What changes do I have to make if I need to add one more criteria in the location column?

For example,
Qualification = AAA & Location = XX
Qualification = AAA & Location = ZZ.

Is it possible to add multiple criteria in the same column "Location"?

The result I am looking for is A, C, and D (not particularly in this order. all these three should be coming as results).

Please help.

Thanks in advance for your support.

 

[Fluff]

Maybe

Book1
	A	B	C	D	E	F	G
1	Name	Age	Qualification	Location		XX	AAA
2	A	21	AAA	XX		YY	A
3	B	21	AAA	YY			B
4	C	25	BBB	ZZ			D
5	D	21	AAA	XX
6	E	20	BBB	XX
Roster

Cell Formulas
Range	Formula
G2	=IFERROR(INDEX($A$2:$A$6,AGGREGATE(15,6,(ROW($A$2:$A$6)-ROW($A$2)+1)/(($C$2:$C$6=$G$1)*(($D$2:$D$6=$F$1)+($D$2:$D$6=$F$2))),ROWS($A$1:$A1))),"")

 

[dpkdileep]

Thanks for the reply. It worked well. I have tweaked it a bit to match my requirements. Apologies from my side for not saying this at the time of answer.

 

[Fluff]

You're welcome & thanks for the feedback