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

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

<colgroup><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[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...

Row\Col

A​

B​

C​

D​

E​

G​

H​

I​

J​

K​

L​

M​

1​

CDS Code

School Type

Last Name

First Name

SEIS ID

CDS code

8528529​

4​

2​

8528529​

56​

Ramirez

Anna

8579645​

Matches (Idx)

SEIS ID

First Name

Last Name

School Type

3​

1258796​

56​

Hall

Maurice

582694​

Type to Include​

Type to Exclude​

4​

459687​

Maya

Hall

70​

4​

1485796​

70​

Hewett

CeDoshi

1115628​

70​

56​

6​

2509684​

Hannah

Yetman

70​

5​

8528529​

70​

Hall

Maya

459687​

71​

8​

1598632​

Haylee

Mercado

71​

6​

8526974​

70​

Smith

Aaron Jaden

156328​

72​

11​

2596842​

Shyanne

Hall

72​

7​

8528529​

70​

Yetman

Hannah

2509684​

8​

1587489​

70​

Coleman

Gloria

5566985​

​

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​

​

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.