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]

 

[Aladin Akyurek]

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.

In I1 just enter:

=COUNTIFS(A2:A13,H1)

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,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]

Aladian thank you soooooo much. Is there a way to make it be in alphabetical order by the last name?

 

[Aladin Akyurek]

Aladian thank you soooooo much. Is there a way to make it be in alphabetical order by the last name?

The best way is to sort your source data on that column in ascending order.

 

[amagana]

Hi Aladin,

I am working on the spreadsheet again and I would like to know if you could explain to me how formula #1 interacts with formula #2 ? I am asking because formula #1 is reporting the correct number but formula #2 is report the incorrect cell location, and therefore the wrong student is showing up.

Formula #1
In I1 just enter:
=COUNTIFS(A2:A13,H1)

Formula #2
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,ROW($E$2:$E$13)-ROW($E$2)+1),ROWS($I$3:I3)))

The data has changed slightly. The formulas I am using are below.

Formula #1
{=COUNTIFS('DINC Data'!$B$2:$B$55000,$D$5+0,'DINC Data'!$H$2:$H$55000,"=10",'DINC Data'!$I$2:$I$55000,">=7/1/2017",'DINC Data'!$K$2:$K$55000,">=61")}

Formula #2
{=IF(ROWS($B$458:B458)>$T$3,"",SMALL(IF('DINC Data'!$B$2:$B$55000=$D$5+0,ROW('DINC Data'!$E$2:$E$55000)-ROW('DINC Data'!$E$2)+1),ROWS($B$458:B458)))}

 

[Aladin Akyurek]

Which set are you questioning? the set of the last two?