copying VLOOKUP

[Jane]

I have copied my vlookup formula across 25 columns and need to reference 25
columns on a data tab. My first formula is:
=vlookup($C20,'data$A$1:$a$50',2,false).... I need to changed the column
lookup for the remaining 24 columns to =vlookup(.....,3,false)
=vlookup(.....,4,false) and so on.

Is there a faster way to change the columns #'s rather than manually
changing them?

thank you in advance! jane

 

[Peo Sjoblom]

Replace 3 with

COLUMNS($C:E)

and copy across will return 3, 4, 5 and so on

having said that if you use $A$1:$A$50 as a table you can only lookup one
column so maybe that part was a typo

--
Regards,

Peo Sjoblom

Portland, Oregon

 

[Jane]

Hi Peo, I think I might not have been clear and/ or perhaps I did not
understand your response.
My formula is =vlookup($C20,'data!$A$1:$A$25',2,false). I have to look up
24 colums of information from the data tab, starting in column 2. I wanted
to know a fast way to copy that formula without having to manually change the
column index number.

I tried your solution without luck. I do appreciate any help on this.
Thanks! Jane

 

[Peo Sjoblom]

Your formula as posted is incorrect, your formula is looking in the second
column of a table that is only one column, there is no way your formula will
return anything but an error even after you have removed the apostrophe
after A25.
So it doesn't surprise me that my solution does not work. As I noted in my
previous answer:

"having said that if you use $A$1:$A$50 as a table you can only lookup one
column so maybe that part was a typo"

now you post the same formula again which makes me believe it was not a
typo?

You need to refer to a table in a vlookup formula with as many columns as
there are index numbers meaning that if your table starts in A1 and want to
return values from 25 columns it needs at least be


=VLOOKUP($C20,data!$A$1:$Y$25,COLUMNS($A:B),FALSE)

will be the same as

=VLOOKUP($C20,data!$A$1:$Y$25,2,FALSE)

and copied across it will return column index 3, 4, 5 and so on

--
Regards,

Peo Sjoblom

Portland, Oregon

 

[Jane]

ah Peo, of course you are right that I DID have a typo, not only once but
twice! argh - working far too hard I think.... note to self: ask for a day
off.

your solution was perfect - once I corrected my error
thank you for your help - and your patience. j

 

[Jane]

can I induldge in a 2nd related question?

is there a way to vlookup data that has a column index # to the left of the
lookup value? I've always looked data to the right.
As before , thanks for your help! jane