List all names that contains specific text.

[charlesav]

Hi -

Can anyone help me with what formula to use on my sheet? I have two sheets Sheet 1 contains the
1. Name (Column A)
2. Grade (Column B)
3. Position (Column C)

In sheet two, If the Position is Nurse III, I need the names to be included in the Nurse III Column which is on Column A including the Grade (which I can maybe do by doing Vlookup), Nurse IV in Column B and so on.

Can someone help me with the right formula? Thank you so much.

[Glenn Kennedy]

In A2, copied across to B2 and down:

=IFERROR(INDEX(Sheet1!A:A,AGGREGATE(15,6,ROW(Sheet1!$A$2:$A$20)/(Sheet1!$C$2:$C$20=$A$1),ROWS(A$2:A2))),"")

adjust the ranges appropriately for the remaining columns.

[Glenn Kennedy]

or... delete " NAME" from C1 and E1.

In A2:
=IFERROR(INDEX(Sheet1!$A:$A,AGGREGATE(15,6,ROW(Sheet1!$A$2:$A$20)/(Sheet1!$C$2:$C$20=A$1),ROWS(A$2:A2))),"")

In B2:
=IFERROR(INDEX(Sheet1!$B:$B,AGGREGATE(15,6,ROW(Sheet1!$A$2:$A$20)/(Sheet1!$C$2:$C$20=A$1),ROWS(B$2:B2))),"")

Select both A2 and b2, copy across and down.

[charlesav]

Thank you so much!

[Glenn Kennedy]

You're welcome!