Multiple Criteria and Multiple Returns Excel

Question

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 ! '

Can you please help??

Thank you so much!

Harun24hr · Accepted Answer · 2016-02-19 04:40:02Z

1

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.

answered Feb 19, 2016 at 4:40

Harun24hr

38.1k6 gold badges24 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

JK0124 Over a year ago

Yes! That did the tick! Would it matter if the Student IDs were on a different sheet?

Harun24hr Over a year ago

No, it wouldn't be matter. It will also work fine. Just refer sheet name.

Collectives™ on Stack Overflow

Multiple Criteria and Multiple Returns Excel

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related