Retrieve multiple values with only one specified in multiple vertical rows

[Excelquestion35]

Hi all,

For a project I need to retrieve all supervisor names belonging to a certain customer in a sort of list view. The retrieved values serve as input for a drop down list.
I tried using VLOOKUP but then I will retrieve only the first value, but actually I need all separate values in multiple cells (amount is equal to the amount of unique supervisors)

I tried the following:

=VLOOKUP($D$1;'[Site overview (1).xlsx]FLMs'!$B:$C;2;FALSE)

D1 is the name of the customer
Column B are all customer names in another file
Column C are all supervisor names in another file

Any idea how I can achieve this?

 

[RasGhul]

Maybe this may help, note that all sheets must be open for the formula to work

Site Overview 1.xlsx
	A	B
1	Customer	Supervisor
2	Customer9	Supervisor1
3	Customer2	Supervisor2
4	Customer3	Supervisor3
5	Customer4	Supervisor4
6	Customer9	Supervisor5
7	Customer6	Supervisor6
8	Customer9	Supervisor7
9	Customer8	Supervisor8
10	Customer9	Supervisor9
11	Customer10	Supervisor10
Sheet1

Extract.xlsx
	A	B
2	Customer	Customer9
3	Supervisor	Supervisor1
4
5	Supervisor List
6	Supervisor1
7	Supervisor5
8	Supervisor7
9	Supervisor9
Sheet1

Cell Formulas
Range	Formula
A6:A9	A6	=IF(ROWS($A$6:A6)>COUNTIF('[Site Overview 1.xlsx]Sheet1'!$A$2:$A$100000,$B$2),"",INDEX('[Site Overview 1.xlsx]Sheet1'!$B:$B,SMALL(IF('[Site Overview 1.xlsx]Sheet1'!$A:$A=$B$2,ROW('[Site Overview 1.xlsx]Sheet1'!$A$2:$A$100000)-ROW('[Site Overview 1.xlsx]Sheet1'!$A$1)),ROWS($A$6:A6))))
Press CTRL+SHIFT+ENTER to enter array formulas.

Named Ranges
Name	Refers To	Cells
Supervisor	=Sheet1!$A$6:INDEX(Sheet1!$1:$100000,COUNTA('[Site Overview 1.xlsx]Sheet1'!$A$2:$A$100000),1)	A6:A9

Cells with Data Validation
Cell	Allow	Criteria
B3	List	=Supervisor