Using Index match and small to come up with different results from same lookup

See if this is what you had in mind.

Data Range

	A	B	C	D	E
1	Employee	Shift	------	Shift	Employee
2	Bill	1		1	Bill
3	Bob	2			Betty
4	Beth	3			Bo
5	Betty	1			Brad
6	Bernard	2
7	Bo	1
8	Babs	3
9	Bridget	2
10	Brad	1
11	Barbra	3

We want to list all employees on 1st shift.

This array formula** entered in E2:

=IFERROR(INDEX(A:A,SMALL(IF(B$2:B$11=D$2,ROW(B$2:B$11)),ROWS(E$2:E2))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down until you get blanks.

966.3 KB

Sorry, that's way too big for me to download.

--edit formula =IFERROR(INDEX($A:$A,SMALL(IF(B$2:B$12=$H$1,ROW(B$2:B$12)),ROWS(B$2:B2))),"")
Change ROWS to B$2:B2

Hello Sirbletch,

I would set this up in columns. Please see attachment. upload2.xlsx

Per Tony's formula, I edited to pull the names into time range.

=IFERROR(INDEX($A:$A,SMALL(IF(B$2:B$12=$H$1,ROW(B$2:B$12)),ROWS(E$2:E2))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Thanks,
Newb

Good deal. Thanks for the feedback!

If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.