Lookup value with multipe Results

[John]

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

 

[Sheeloo]

You got it ALMOST right...

Use this in the cell you want the first value
=INDEX($P$1:$S$2500,SMALL(IF($P$1:$P$2500=$A$3,ROW($P$1:$P$2500)),ROW(1:1)),4)
and press CTRL-SHIFT-ENTER
then copy down

I changed $P$3 to $P$1 since you are counting the rows from 1....

Also changed ROW($1:$1) to ROW(1:1) since you want it to be ROW(2:2) in the
second row.

 

[Sheeloo]

Also changed $A3 to $A$3 since you want to find the match for the same value.

 

[John]

I want to be able to drag the formula down the sheet to search for A3 then A4
then A5 so an so on. Wont this lock it up and not allow that number to
change? Also if I want the second answer to show up in the cell to the right
of the first what makes that work? The first formula is working great by the
way. Thanks

 

[T. Valko]

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$20,A$3),INDEX(S$3:S$20,SMALL(IF(P$3$20=A$3,ROW(S$3:S$20)),ROWS(U$3:U3))-MIN(ROW(P$3$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$20,A$3),SMALL(IF(P$3$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.

 

[John]

I think I found a work around for the previous question I am now using the
formula below which changes the referenced cell as I autofill down. However I
am getting #NUM! on some cells where there is no data found. I thought I put
no errors in the formula. Anyhelp?


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