Vlookup Formula

[Manos]

Dear All

i have in column A almost 5000 lines
but in some rows have a word with name "TOT" where in
column b there is a number
so i want to make it to bring in another sheet this
numbers seperatly.
i though to bring it one by one
so i have to use the VLOOKUP formula.
But where i have to put the value TOT it wil bring me only
one number.
?How can i make it to search one by one the TOT and bring
me all the values to the other sheet.

EG:

A B C
TOT 45
1
2
3
TOT 32
34
25
TOT 5
67
82
12
TOT 6

I WANT in the other sheet to see
TOT 45
TOT 32
TOT 5
TOT 6

HOW

Thanks in advance
Manos

 

[Andy B]

Manos

It might be easier to use Data / Filter / Autofilter with TOT as the
criteria. This will limit the sheet to displaying the lines you want. From
there, hit Ctrl * to select the whole area. You can then copy this and paste
it where you wish.

Andy.

 

[Frank Kabel]

Hi Manos
on your second sheet in row one (e.g. in cell B1) enter the following
array formula (entered with CTRL+SHIFT+ENTER)
=IF(ISERROR(INDEX('sheet1'!$A$1:$A$1000,SMALL(IF('sheet1'!$B$1:$B$1000
=
"TOT",ROW('sheet1'!$B$1:$B$1000),1001),ROW()))),"",INDEX('sheet1'!$A$1:
$A$1000,SMALL(IF('sheet1'!$B$1:$B$1000 =
"TOT",ROW('sheet1'!$B$1:$B$1000),1001),ROW())))
and copy down

in A1 enter the following
=IF(B1<>"","TOT","")
and copy down