How can I retreive rows from one column?

M

Miriam

My Ex:
Sheet 1
A B
1 100
2 5
6 12
2 200
I need in another sheet ,When I write 2 i can retreive 5 , 200 , ........
A B
2 5
200

I can't do it with LookUp Equations.

Thanks,
Waiting for reply
 
D

Domenic

Assumptions:

Sheet1!A1:B4 contains your source data

Sheet2!A1 contains your criteria, such as 2

Formula:

Sheet2!B1, copied down:

=IF(ROWS(B$1:B1)<=COUNTIF(Sheet1!$A$1:$A$4,$A$1),INDEX(Sheet1!$B$1:$B$4,S
MALL(IF(Sheet1!$A$1:$A$4=$A$1,ROW(Sheet1!$B$1:$B$4)-ROW(Sheet1!$B$1)+1),R
OWS(B$1:B1))),"")

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!
 
C

CLR

You might be interested in the Data > Filter > AutoFilter feature..........

Vaya con Dios,
Chuck, CABGx3
 
Top