Hi!

Try using a filter........

....but, if you want to make this dynamic and use a formula........

Assume you enter the criteria in a cell, say, D1:

D1 = y1

Entered as an array using the key combo of CTRL,SHIFT,ENTER:

=IF(ROWS($1:1)<=COUNTIF(A$2:A$7,D$1),INDEX(C$2:C$7 ,SMALL(IF(A$2:A$7=D$1,ROW(A$2:A$7)-ROW(A$2)+1),ROWS($1:1))),"")

Copy down until you get blanks.

Biff

"David127" wrote in message
...
An array or function is needed to create a list based on a single shared
criteria.

ID# Value Name
y1 5 Jill
x2 7 Jack
x3 3 Mary
y1 15 James
y1 9 Vito
z1 4 Wally


Based on the above table a list selecting ID# y1 would generate:

Jill
James
Vito

Thanks in advance!