How can I make xlookup return all results in a data validation cell drop down list?

[aazmy]

Hello all,

I have a wide range of employees data, I have been using normal Xlookup to get the Person Number by looking up the Employee name. However I came across case similar to the example below, there is a two idfferent employees with two different Person Number, but have the same exact name. So, I was looking for a way to return all the results in drop down list and I can choose the correct one from it.
I tried to put the xlookup into a data validation, but it returned the first result only as shown in the below example.
|Any help of getting all the results in the list?
Thanks in advance.

 

[kevin9999]

Is this the type of thing you mean?

Book1
	A	B
8	Person_Number	Person_Name
9	219518	Ahmed Mohamed
10	247121	Hossam Alaa
11	103985	Abdelrahman Mohamed
12	277164	Ahmed Mohamed
13
14
15	Employee_Name	Person_Number
16	Ahmed Mohamed	219518
17		277164
Sheet1

Cell Formulas
Range	Formula
B16:B17	B16	=FILTER($A$9:$A$12,$B$9:$B$12=A16,"")
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
A16	List	=$B$9:$B$12

 

[Peter_SSs]

Welcome to the MrExcel board!

For the future I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

Could you use something like this?
Use some helper columns. I have used D, E, ... but they can be any columns and they could be hidden once you enter the formula shown in D16 and copied down as far as you might ever need.
Then set up the Data validation as shown.

23 03 12.xlsm
	A	B	C	D	E	F
8	Number	Name
9	219518	Name 1
10	247121	Name 2
11	103985	Name 3
12	277164	Name 1
13
14
15	Name	Number
16	Name 1			219518	277164
17	Name 3			103985
18	Name 1			219518	277164
19	Name 2			247121
20
21
DV

Cell Formulas
Range	Formula
D16:E16,D18:E18,D17,D19:D21	D16	=TRANSPOSE(FILTER($A$9:$A$12,$B$9:$B$12=A16,""))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
B16:B19	List	=D16#

This is the result when completing the Number for Name 1

 

[Peter_SSs]

Actually, this would be more compact and "neater". Rather than copying down all the rows, just set up this top-right section and the altered DV as shown.

23 03 12.xlsm
	A	B	C	D	E	F	G	H
8	Number	Name
9	219518	Name 1			Name 1	219518	277164
10	247121	Name 2			Name 2	247121
11	103985	Name 3			Name 3	103985
12	277164	Name 1
13
14
15	Name	Number
16	Name 1
17	Name 3
18	Name 1
19	Name 2
DV (2)

Cell Formulas
Range	Formula
E9:E11	E9	=UNIQUE(B9:B12)
F9:G9,F10:F11	F9	=TRANSPOSE(FILTER($A$9:$A$12,$B$9:$B$12=E9,""))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
B16:B19	List	=XLOOKUP(A16,$E$9:$E$11,$F$9:$F$11)#