My appologies... I was trying multiple variations & copied both example
array's incorrectly. The range is indeed A2:A776 & even when its the same I
get a error #N/A. However when I make the range A2:A339 I get the appropriate
rusult. If I bring the range to A2:A340 or higher the SMALL function does not
return a value.

"Biff" wrote:

Hi!

One question:

Is the range in Identifier column A, A2:A777, or A2:A776?

You're using A2:A777 in the Countif function and A2:A776 everywhere else?
They all need to be the same.

Try this (using 2:776 in all ranges)

=IF(ROWS($1:1)<=COUNTIF(Identifier!A$2:A$776,E$2), INDEX(Identifier!C$2:C$776,SMALL(IF(Identifier!A$2 :A$776=E$2,ROW(A$2:A$776)-ROW(A$2)+1),ROWS($1:1))),"")

Biff

"David127" wrote in message
...
Hi Biff-

I tested out your array with my example & it worked like a charm!
Howerver,
I expanded the array to cover a much larger range & I'm getting the #NUM!.
Below are the two array's for comparison (your's is altered to reflect
similar column's, worksheets etc).


Your example:
=IF(ROWS($2:10)<=COUNTIF(Identifier!A$2:A$777,E$2) ,INDEX(Identifier!C$2:C$776,SMALL(IF(Identifier!A$ 2:A$776=E$2,ROW(Identifier!A$2:A$776)-ROW(Identifier!A$2)+1),ROWS($2:10))),"")

Mine:
=IF(ROWS($2:10)<=COUNTIF(Identifier!A$2:A$777,E$2) ,INDEX(Identifier!C$2:C$776,SMALL(IF(Identifier!A$ 2:A$776=E$2,ROW(Identifier!A$2:A$776)-ROW(Identifier!A$2)+1),ROWS($2:10))),"")


Using the Formula Palet the "Small" function in my example does not return
a
value, just a blank. Have I exceeded its limit? If so are there any
options
to your array.

Many Thanks!


"Biff" wrote:

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!