match a value in column P.... return the result from column S.

Try this robust version:

Assume you want the results starting in cell U3.

Enter this array formula** in U3 and copy down until you get blanks meaning
all data has been extracted:

=IF(ROWS(U$3:U3)<=COUNTIF(P$3:P$20,A$3),INDEX(S$3: S$20,SMALL(IF(P$3:P$20=A$3,ROW(S$3:S$20)),ROWS(U$3 :U3))-MIN(ROW(P$3:P$20))+1),"")

If your data to be returned in numeric then you can use a less complex
formula that will return the results in either ascending or descending
order:

For ascending order:

=IF(ROWS(U$3:U3)<=COUNTIF(P$3:P$20,A$3),SMALL(IF(P $3:P$20=A$3,S$3:S$20),ROWS(U$3:U3)),"")

For descending order just replace SMALL with LARGE.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"John" wrote in message
...
I am using the following formula and have tried numerous variations with no
luck.

=INDEX($P$3:$S$2500,SMALL(IF($P$3:$P$2500=$A3,ROW( $P$3:$P$2500)),ROW($1:$1)),4)

I am trying to find the value in Cell A3 and return results from the array
of column P through S. The value that is in Cell A3 will match a value in
column P. I want to return the result from column S.


Basically Cell A3 = TEST1, Cell P24, P25, P50 = Test1, and Cell S24, S25,
S50 = 94, 92, 88 (respectively)

When I use the above formula I should be able to return the first result
then modify the formula and return the second result and so on? I am not
sure
what I am missing or if I am on the right track. Any ideas? Thanks