Displaying data based on list selection

[eoindub]

Hi all,

Could anyone assist in what is probably a fairly simple thing but one which I can't figure out despite much research.

I have a table containing supervisors and the corresponding executive they report to. I have a drop down list in a separate sheet of those same executives and I need to return the list of supervisors under that executive when selected. i.e. when Exec1 is selected, the entire list of corresponding supervisors will be displayed underneath.

See the example attached. Sheet 1 contains the table. Sheet 2 contains the drop down list and I need to display the corresponding supervisors there depending on the exec selected. Can anyone help?List selection example.xlsx

[samba_ravi]

=IFERROR(INDEX(Sheet1!$A:$A,SMALL(INDEX((Table2[Exec]<>$B$2)*10^10+ROW(Table2[Exec]),0),ROW(1:1))),"")
Try this in Cell B5 of Sheet2 and copy towards down

[eoindub]

Thanks! After a bit of modification to work with the larger data set I have, that worked a treat!

[samba_ravi]

Thanks for your feedback

[eoindub]

Sorry one other question - do you know why this formula will not return #N/A values from the supervisor list? Is it possible to include these?

[samba_ravi]

=IFERROR(INDEX(Sheet1!$A:$A,SMALL(INDEX((Table2[Exec]<>$B$2)*10^10+ROW(Table2[Exec]),0),ROW(1:1))),"")
Here we used iferror function when the #N/A occurs iferror formula returns "" (Blank)
see the Blue color part "", you can change as per your requirement