Formula Help... Needed

[jamesmev]

I am currently working with a list of 20K plus names. Each name has a "supervisor name" associated with it.

However, I am trying to utilize this formula to show all people under specific supervisors when one is listed.

=IFERROR(INDEX($C$2:$C$20520,SMALL(IF($V$2=$I$2:$I$20520,ROW($I$2:$I20520)-ROW($I$2)+1),ROW(1:1))),"")

This is only pulling the first name within the list that is associated - when in fact there are over 10 throughout the entirety of the list.

Example:

Supervisor:
John Smith (V2)

Direct Reports
Bob
Mary
Sarah
Paul
Jack
Jill
Ralph
Mike

My formula would be only pulling BOB

 

[lillicob]

It might be the way you copied and pasted the formula, but it looks like you need to use crtl+shift+enter to make it an array formula and then drag it down so that it will fill in remaining cells.

 

[Marcelo Branco]

It's missing a $ and also i suggest a small change to make the formula more robust (in blue).

=IFERROR(INDEX($C$2:$C$20520,SMALL(IF($V$2=$I$2:$I$20520,ROW($I$2:$I$20520)-ROW($I$2)+1),ROWS(A$1:A1))),"")
Ctrl+Shift+Enter

M.

 

[jamesmev]

Thank you for the replies. I did have the Ctrl shift enter.... But the above did work. Thank you all so much.

 

[Marcelo Branco]

You are welcome. Glad to help.

M.