Combining lookup functions with data validation

[ieumts]

Hi
I have a table like this in A1:B11:

Name Code
Fred 3511
Jack 3512
Alice 3513
Lora 3514
John 3515
Ann 3516
Fred 3517
Lora 3518
Alice 3519
Fred 3520

for example I want to Lookup all codes of Fred by drop down list in data validation.
Thanks

[southward]

Hi,

There are several User Defined Functions (UDF) that can do what you are asking.

The closest I can find was found on:

http://superuser.com/questions/53623...ultiple-values

See attached example with the UDF.

Hope this works for you.

Cheers

[JohnTopley]

In I2

=IFERROR(INDEX(B:B,SMALL(IF($A$2:$A$11=$F$2,ROW($A$2:$A$11),""),ROWS($1:1))),"")

Enter with Ctrl+Shift+Enter

This is named range "Codes" and is codes for name in F2

in column J

List of names

Named range "Names"

F2 is DV drop down

=List

=NAmes

G2 is drop-down

=List

=Codes

[Tony Valko]

If you want the results to appear in a single cell like this:

Data Range

	F	G
1	Name	Code
2	Fred	3511,3517,3520

It will require a VBA function.