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

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),I NDEX(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!

In article ,
Miriam wrote:

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

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

Vaya con Dios,
Chuck, CABGx3



"Domenic" wrote:

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),I NDEX(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!

In article ,
Miriam wrote:

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