Combing INDEX, MATCH, SMALL, with multiple criteria

[amagana]

Good Afternoon,

I am needing a little help with a formula. What my goal is to have values reported back in a table below. The formula I have listed below works but currently, there is only one Criteria. The first criteria is Look up 8528529 in Column H and then report back the students SEIS ID #. But what I am now trying to accomplish is that only students in School type 70, 71, or 72 be reported back not school type 56. Any help that can be provided would be greatly appreciated.


{=IF(ISERROR(INDEX($H$1:$L$26,SMALL(IF($H$1:$H$26=$O$1,ROW($H$1:$H$26)),ROW(2:2)),5)),"",INDEX($H$1:$L$26,SMALL(IF($H$1:$H$26=$O$1,ROW($H$1:$H$26)),ROW(2:2)),5))}


Columns
H I J K L N O

[TABLE="width: 783"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]CDS Code [/TD]
[TD]School Type[/TD]
[TD]Last Name[/TD]
[TD]First Name[/TD]
[TD]SEIS ID[/TD]
[TD][/TD]
[TD]Look up[/TD]
[TD]8528529[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8528529[/TD]
[TD]56[/TD]
[TD]Ramirez[/TD]
[TD]Anna[/TD]
[TD]8579645[/TD]
[TD][/TD]
[TD]Lookup[/TD]
[TD]>=70[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1258796[/TD]
[TD]56[/TD]
[TD]Hall[/TD]
[TD]Maurice[/TD]
[TD]582694[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1485796[/TD]
[TD]70[/TD]
[TD]Hewett[/TD]
[TD]CeDoshi[/TD]
[TD]1115628[/TD]
[TD][/TD]
[TD]Matches[/TD]
[TD]SEIS ID[/TD]
[TD]First Name[/TD]
[TD]Last Name[/TD]
[TD]School Type[/TD]
[/TR]
[TR]
[TD]8528529[/TD]
[TD]70[/TD]
[TD]Hall[/TD]
[TD]Maya[/TD]
[TD]459687[/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]8526974[/TD]
[TD]70[/TD]
[TD]Smith[/TD]
[TD]Aaron Jaden[/TD]
[TD]156328[/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]8528529[/TD]
[TD]70[/TD]
[TD]Yetman[/TD]
[TD]Hannah[/TD]
[TD]2509684[/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]1587489[/TD]
[TD]70[/TD]
[TD]Coleman[/TD]
[TD]Gloria[/TD]
[TD]5566985[/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]8528529[/TD]
[TD]71[/TD]
[TD]Mercado[/TD]
[TD]Haylee[/TD]
[TD]1598632[/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]5569823[/TD]
[TD]71[/TD]
[TD]Kendall[/TD]
[TD]Marleny[/TD]
[TD]2587032[/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]4582679[/TD]
[TD]72[/TD]
[TD]Troy[/TD]
[TD]bella[/TD]
[TD]3206584[/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]8528529[/TD]
[TD]72[/TD]
[TD]Hall[/TD]
[TD]Shyanne[/TD]
[TD]2596842[/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]1158964[/TD]
[TD]72[/TD]
[TD]Ramirez[/TD]
[TD]Ariane[/TD]
[TD]2514895[/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

[Nishant94]

Unknown
	H	I	J	K	L	M	N	O	P	Q	R
1	CDS Code	School Type	Last Name	First Name	SEIS ID		Look up	8528529
2	8528529	56	Ramirez	Anna	8579645		Lookup	70
3	1258796	56	Hall	Maurice	582694
4	1485796	70	Hewett	CeDoshi	1115628		Matches	SEIS ID	First Name	Last Name	School Type
5	8528529	70	Hall	Maya	459687			459687	Maya	Hall	70
6	8526974	70	Smith	Aaron Jaden	156328			2509684	Hannah	Yetman	70
7	8528529	70	Yetman	Hannah	2509684			1598632	Haylee	Mercado	71
8	1587489	70	Coleman	Gloria	5566985			2596842	Shyanne	Hall	72
9	8528529	71	Mercado	Haylee	1598632
10	5569823	71	Kendall	Marleny	2587032
11	4582679	72	Troy	bella	3206584
12	8528529	72	Hall	Shyanne	2596842
13	1158964	72	Ramirez	Ariane	2514895
14
15
Sheet19

Cell Formulas
Range	Formula
O5	{=IFERROR(INDEX(INDEX($I$2:$L$13,0,MATCH(O$4,$I$1:$L$1,0)),SMALL(IF(($O$1=$H$2:$H$13)*($I$2:$I$13>=$O$2),ROW($H$2:$H$13)-ROW($H$2)+1),ROWS(O$5:O5))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Aladin Akyurek]

Here is a more efficient set up...

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

G​

[/td][td]

H​

[/td][td]

I​

[/td][td]

J​

[/td][td]

K​

[/td][td]

L​

[/td][td]

M​

[/td][/tr][tr][td]

1​

[/td][td]CDS Code[/td][td]School Type[/td][td]Last Name[/td][td]First Name[/td][td]SEIS ID[/td][td]CDS code[/td][td]

8528529​

[/td][td]

4​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

2​

[/td][td]

8528529​

[/td][td]

56​

[/td][td]Ramirez[/td][td]Anna[/td][td]

8579645​

[/td][td][/td][td][/td][td]Matches (Idx)[/td][td]SEIS ID[/td][td]First Name[/td][td]Last Name[/td][td]School Type[/td][/tr]
[tr][td]

3​

[/td][td]

1258796​

[/td][td]

56​

[/td][td]Hall[/td][td]Maurice[/td][td]

582694​

[/td][td]

Type to Include​

[/td][td]

Type to Exclude​

[/td][td]

4​

[/td][td]

459687​

[/td][td]Maya[/td][td]Hall[/td][td]

70​

[/td][/tr]
[tr][td]

4​

[/td][td]

1485796​

[/td][td]

70​

[/td][td]Hewett[/td][td]CeDoshi[/td][td]

1115628​

[/td][td]

70​

[/td][td]

56​

[/td][td]

6​

[/td][td]

2509684​

[/td][td]Hannah[/td][td]Yetman[/td][td]

70​

[/td][/tr]
[tr][td]

5​

[/td][td]

8528529​

[/td][td]

70​

[/td][td]Hall[/td][td]Maya[/td][td]

459687​

[/td][td]

71​

[/td][td][/td][td]

8​

[/td][td]

1598632​

[/td][td]Haylee[/td][td]Mercado[/td][td]

71​

[/td][/tr]
[tr][td]

6​

[/td][td]

8526974​

[/td][td]

70​

[/td][td]Smith[/td][td]Aaron Jaden[/td][td]

156328​

[/td][td]

72​

[/td][td][/td][td]

11​

[/td][td]

2596842​

[/td][td]Shyanne[/td][td]Hall[/td][td]

72​

[/td][/tr]
[tr][td]

7​

[/td][td]

8528529​

[/td][td]

70​

[/td][td]Yetman[/td][td]Hannah[/td][td]

2509684​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

8​

[/td][td]

1587489​

[/td][td]

70​

[/td][td]Coleman[/td][td]Gloria[/td][td]

5566985​

[/td][td]

​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

9​

[/td][td]

8528529​

[/td][td]

71​

[/td][td]Mercado[/td][td]Haylee[/td][td]

1598632​

[/td][td]

​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

10​

[/td][td]

5569823​

[/td][td]

71​

[/td][td]Kendall[/td][td]Marleny[/td][td]

2587032​

[/td][td]

​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

11​

[/td][td]

4582679​

[/td][td]

72​

[/td][td]Troy[/td][td]bella[/td][td]

3206584​

[/td][td]

​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

12​

[/td][td]

8528529​

[/td][td]

72​

[/td][td]Hall[/td][td]Shyanne[/td][td]

2596842​

[/td][td]

​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

13​

[/td][td]

1158964​

[/td][td]

72​

[/td][td]Ramirez[/td][td]Ariane[/td][td]

2514895​

[/td][td]

​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

In I1 control+shift+enter, not just enter:

=SUM(IF($A$2:$A$13=$H$1,IF(ISNA(MATCH($B$2:$B$13,$H$4:$H$4,0)),IF(ISNUMBER(MATCH($B$2:$B$13,$G$4:$G$6,0)),1))))

In I3 control+shift+enter, not just enter, and copy down:

=IF(ROWS($I$3:I3)>$I$1,"",SMALL(IF($A$2:$A$13=$H$1,IF(ISNA(MATCH($B$2:$B$13,$H$4:$H$4,0)),IF(ISNUMBER(MATCH($B$2:$B$13,$G$4:$G$6,0)),ROW($E$2:$E$13)-ROW($E$2)+1))),ROWS($I$3:I3)))

In J3 just enter, copy across, and down:

=IF($I3="","",INDEX($A$2:$E$13,$I3,MATCH(J$2,$A$1:$E$1,0)))

 

[amagana]

Thank you for your help. This worked. Is it possible to distinguish that I only want 70-72 to be reported for the school type? Right now we have >=$O$2. 70 is listed in O2. I have tried using AND to include the other cells but that did not work. I also tried >=$O$2 and then <= and listed the cell with 72 as the school type. That did not work.

 

[Aladin Akyurek]

Thank you for your help. This worked. Is it possible to distinguish that I only want 70-72 to be reported for the school type? Right now we have >=$O$2. 70 is listed in O2. I have tried using AND to include the other cells but that did not work. I also tried >=$O$2 and then <= and listed the cell with 72 as the school type. That did not work.

See post #3 , a more efficient set up.

 

[amagana]

Hi Aladin, I tried that option but it was not working for me.

 

[Aladin Akyurek]

Hi Aladin, I tried that option but it was not working for me.

See: https://www.dropbox.com/s/ddb7sx91wuecqch/amagana multiple criteria sublist.xlsx?dl=0

 

[amagana]

Thank you. That worked great!

On another section, I am wanting just to match the CDs code, and report back. There are no other criteria needed, how do I fix the formula?

 

[Aladin Akyurek]

Thank you. That worked great!

You are welcome

On another section, I am wanting just to match the CDs code, and report back. There are no other criteria needed, how do I fix the formula?

Care to provide an example for what you mean?

 

[amagana]

Sure. I would like the formula to report back all of the SEIS IDs from Column E that have the same CDS code (Column A) with H1. The previous formula did that but it added additional criteria of having to be in a certain school type. On this other table that I am working on I no longer need the additional criteria.