Vlookup Formula

  • Thread starter Thread starter Manos
  • Start date Start date
M

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
 
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.
 
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
 
Back
Top