Search code examples
vbaexcelexcel-formulaarray-formulas

Multiple Criteria and Multiple Returns Excel

It's been a while since I've been here. I've been struggling with a formula on Excel using multiple lookups giving multiple returns. In this sheet, the inputs are: Location, Subject, Level.

I used the following formula to return the teacher's name in H4: =INDEX(D2:D26, MATCH(1, (H1=A2:A26)(H2=B2:B26)(H3=C2:C26), 0))

I'm trying to have it return the multiple student IDs. With the following inputs: Location Lookup: U

Subject Lookup: QC

Level Lookup: 2

I'm expecting the following student IDs being returned, but I'm not sure of how to solve this.
1012, 1013, 1014, 1015, 1016, 1017, 1018 ! 'Excel Image

Can you please help??

Thank you so much!

Solution

  • Use this array formula in H5 cell to get student IDs and fill down as you need.

    =IFERROR(INDEX($E$1:$E$26,SMALL(IF(($A$1:$A$26=$H$1)*($B$1:$B$26=$H$2)*($C$1:$C$26=$H$3),ROW($D$1:$D$26)),ROWS($A$1:$A1))),"")

    As it is a array formula, Press CTRL+SHIFT+ENTER to evaluate the formula.