I have a need to do a VLOOKUP in twenty columns on the destination sheet. I
want to pull 20 columns of data in exactly the same order onto the
destination sheet from the original sheet. I know the range can be made
relative or absolute, but I'd like the column index number to increment when
I copy my formula into the next cell to the right. Does anyone know how to
do this quickly and easily instead of going in and manually changing the
column index number in each formula across the sheet. Example
VLOOKUP($A2,Sheet2!$A$1:$Z$26,2,FALSE) in the first cell then
($A2,Sheet2!$A$1:$Z$26,3,FALSE) in the next cell to the right. Note the
column index number changed from 2 to 3.

Hello,

you can use the column index number refering to a cell
and on that cell use =counta(sheet2!1:1)

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Nicky Armstrong" escreveu:

I have a need to do a VLOOKUP in twenty columns on the destination sheet. I
want to pull 20 columns of data in exactly the same order onto the
destination sheet from the original sheet. I know the range can be made
relative or absolute, but I'd like the column index number to increment when
I copy my formula into the next cell to the right. Does anyone know how to
do this quickly and easily instead of going in and manually changing the
column index number in each formula across the sheet. Example
VLOOKUP($A2,Sheet2!$A$1:$Z$26,2,FALSE) in the first cell then
($A2,Sheet2!$A$1:$Z$26,3,FALSE) in the next cell to the right. Note the
column index number changed from 2 to 3.

Assume you enter the first formula in cell B2.

=VLOOKUP($A2,Sheet2!$A$1:$Z$26,COLUMNS($A2:B2),0)

Copy across as needed.

--
Biff
Microsoft Excel MVP


"Nicky Armstrong" wrote in
message ...
I have a need to do a VLOOKUP in twenty columns on the destination sheet.
I
want to pull 20 columns of data in exactly the same order onto the
destination sheet from the original sheet. I know the range can be made
relative or absolute, but I'd like the column index number to increment
when
I copy my formula into the next cell to the right. Does anyone know how
to
do this quickly and easily instead of going in and manually changing the
column index number in each formula across the sheet. Example
VLOOKUP($A2,Sheet2!$A$1:$Z$26,2,FALSE) in the first cell then
($A2,Sheet2!$A$1:$Z$26,3,FALSE) in the next cell to the right. Note the
column index number changed from 2 to 3.

Change your formula to this:

=VLOOKUP($A2,Sheet2!$A$1:$Z$26,COLUMN(B1),FALSE)

copy it across and the COLUMN(B1) (returning 2) becomes COLUMN(C1),
COLUMN(D1) etc.

Hope this helps.

Pete

"Nicky Armstrong" wrote in
message ...
I have a need to do a VLOOKUP in twenty columns on the destination sheet.
I
want to pull 20 columns of data in exactly the same order onto the
destination sheet from the original sheet. I know the range can be made
relative or absolute, but I'd like the column index number to increment
when
I copy my formula into the next cell to the right. Does anyone know how
to
do this quickly and easily instead of going in and manually changing the
column index number in each formula across the sheet. Example
VLOOKUP($A2,Sheet2!$A$1:$Z$26,2,FALSE) in the first cell then
($A2,Sheet2!$A$1:$Z$26,3,FALSE) in the next cell to the right. Note the
column index number changed from 2 to 3.