Array - search for value, returning neighbouring cell values with no gaps

[dslhs]

Hi,

I needed a similar array formula in the past and was helped hugely by this forum. But I can't replicate for a different context.

I'm looking for an array formula that will allow me to search for a value (in a table in a different sheet), and then return all the neighbouring cell values to the matches. Essentially, I want to be able to enter a class name in one sheet and return all the students in the class (taken from a list of students and their classes in a different sheet). I'd like the array to return the results with no gaps.

Here is the first sheet with the student class/course data:

Class List Excel.xlsx
	D
14
Class List

I've obviously anonymised it, but the real data goes down to row 4293

Here is the second sheet. I'd like to put the search value in cell B1 and return the results A6 downwards.

Class List Excel.xlsx
	A
6
Class Plan

I've got VLOOKUPS setup for the second sheet that will return key info about the students, but that all works. I would just like to find a way of getting the student names to appear based on what class is in B2.

(Later, I might look at creating a list in B2 with data validation so staff can just select from the list, but I don't believe that will matter for this array as it will just create the search value in B2, right?

Thank you!

 

[BSALV]

I've obviously anonymised it

you certainly did !

 

[Peter_SSs]

@dslhs
You need to select the actual range in your worksheet that you want to show before clicking 'Mini Sheet'

 

[dslhs]

my bad!

First sheet:

Class List Excel.xlsx
	A	B
1	Name	Course/Class Name
2	Student A	English A
3	Student A	Maths B
4	Student A	Science A
5	Student A	French C
6	Student A	History A
7	Student A	RE C
8	Student B	English A
9	Student B	Maths A
10	Student B	Science B
11	Student B	French B
12	Student B	History A
13	Student B	RE C
14	Student C	English B
15	Student C	Maths A
16	Student C	Science B
17	Student C	French B
18	Student C	History A
19	Student C	RE A
Class List

Second sheet:

Class List Excel.xlsx
	A	B
1	Class:	English A
2
3
4
5	Student List:
6
7
Class Plan

 

[Rick Rothstein]

Here is one way to do it. You didn't say, so I assume you first sheet was named Sheet1. Put this array-entered** formula on your second sheet in cell B5 and copy down for as many cells as you have students...

=IFERROR(INDEX(Sheet1!A:A,SMALL(IF(Sheet1!B$2:B$19=$B$1,ROW(Sheet1!B$2:B$19)),ROWS(B$5:B5))),"")

**Commit this formula using CTRL+SHIFT+ENTER and not Enter by itself

 

[Peter_SSs]

You didn't say, so I assume you first sheet was named Sheet1.

@dslhs
Thanks for the 'better' XL2BB samples.
If you would prefer to avoid the C+S+E formula confirmation, here is an alternative that does not require it.

dslhs.xlsm
	A	B
1	Class:	English A
2
3
4
5	Student List:	Student A
6		Student B
7
8
Class Plan

Cell Formulas
Range	Formula
B5:B8	B5	=IFERROR(INDEX('Class List'!A:A,AGGREGATE(15,6,ROW('Class List'!A$2:A$5000)/('Class List'!B$2:B$5000=B$1),ROWS(B$5:B5))),"")

 

[dslhs]

Works like a charm. Thank you both

 

[Peter_SSs]

You're welcome. Glad we could help. Thanks for the follow-up.