software

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

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

quot;Janequot; gt; wrote in message
...
gt;I have copied my vlookup formula across 25 columns and need to reference 25
gt; columns on a data tab. My first formula is:
gt; =vlookup($C20,'data$A$1:$a$50',2,false).... I need to changed the column
gt; lookup for the remaining 24 columns to =vlookup(.....,3,false)
gt; =vlookup(.....,4,false) and so on.
gt;
gt; Is there a faster way to change the columns #'s rather than manually
gt; changing them?
gt;
gt; thank you in advance! janeHi 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! Janequot;Peo Sjoblomquot; wrote:

gt; Replace 3 with
gt;
gt; COLUMNS($C:E)
gt;
gt; and copy across will return 3, 4, 5 and so on
gt;
gt; having said that if you use $A$1:$A$50 as a table you can only lookup one
gt; column so maybe that part was a typo
gt;
gt; --
gt; Regards,
gt;
gt; Peo Sjoblom
gt;
gt; Portland, Oregon
gt;
gt;
gt;
gt;
gt; quot;Janequot; gt; wrote in message
gt; ...
gt; gt;I have copied my vlookup formula across 25 columns and need to reference 25
gt; gt; columns on a data tab. My first formula is:
gt; gt; =vlookup($C20,'data$A$1:$a$50',2,false).... I need to changed the column
gt; gt; lookup for the remaining 24 columns to =vlookup(.....,3,false)
gt; gt; =vlookup(.....,4,false) and so on.
gt; gt;
gt; gt; Is there a faster way to change the columns #'s rather than manually
gt; gt; changing them?
gt; gt;
gt; gt; thank you in advance! jane
gt;
gt;

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:

quot;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 typoquot;

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

quot;Janequot; gt; wrote in message
...
gt; Hi Peo, I think I might not have been clear and/ or perhaps I did not
gt; understand your response.
gt; My formula is =vlookup($C20,'data!$A$1:$A$25',2,false). I have to look up
gt; 24 colums of information from the data tab, starting in column 2. I
gt; wanted
gt; to know a fast way to copy that formula without having to manually change
gt; the
gt; column index number.
gt;
gt; I tried your solution without luck. I do appreciate any help on this.
gt; Thanks! Jane
gt;
gt;
gt; quot;Peo Sjoblomquot; wrote:
gt;
gt;gt; Replace 3 with
gt;gt;
gt;gt; COLUMNS($C:E)
gt;gt;
gt;gt; and copy across will return 3, 4, 5 and so on
gt;gt;
gt;gt; having said that if you use $A$1:$A$50 as a table you can only lookup one
gt;gt; column so maybe that part was a typo
gt;gt;
gt;gt; --
gt;gt; Regards,
gt;gt;
gt;gt; Peo Sjoblom
gt;gt;
gt;gt; Portland, Oregon
gt;gt;
gt;gt;
gt;gt;
gt;gt;
gt;gt; quot;Janequot; gt; wrote in message
gt;gt; ...
gt;gt; gt;I have copied my vlookup formula across 25 columns and need to reference
gt;gt; gt;25
gt;gt; gt; columns on a data tab. My first formula is:
gt;gt; gt; =vlookup($C20,'data$A$1:$a$50',2,false).... I need to changed the
gt;gt; gt; column
gt;gt; gt; lookup for the remaining 24 columns to =vlookup(.....,3,false)
gt;gt; gt; =vlookup(.....,4,false) and so on.
gt;gt; gt;
gt;gt; gt; Is there a faster way to change the columns #'s rather than manually
gt;gt; gt; changing them?
gt;gt; gt;
gt;gt; gt; thank you in advance! jane
gt;gt;
gt;gt;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

quot;Peo Sjoblomquot; wrote:

gt; Your formula as posted is incorrect, your formula is looking in the second
gt; column of a table that is only one column, there is no way your formula will
gt; return anything but an error even after you have removed the apostrophe
gt; after A25.
gt; So it doesn't surprise me that my solution does not work. As I noted in my
gt; previous answer:
gt;
gt; quot;having said that if you use $A$1:$A$50 as a table you can only lookup one
gt; column so maybe that part was a typoquot;
gt;
gt; now you post the same formula again which makes me believe it was not a
gt; typo?
gt;
gt; You need to refer to a table in a vlookup formula with as many columns as
gt; there are index numbers meaning that if your table starts in A1 and want to
gt; return values from 25 columns it needs at least be
gt;
gt;
gt; =VLOOKUP($C20,data!$A$1:$Y$25,COLUMNS($A:B),FALSE)
gt;
gt; will be the same as
gt;
gt; =VLOOKUP($C20,data!$A$1:$Y$25,2,FALSE)
gt;
gt; and copied across it will return column index 3, 4, 5 and so on
gt;
gt; --
gt; Regards,
gt;
gt; Peo Sjoblom
gt;
gt; Portland, Oregon
gt;
gt;
gt;
gt;
gt; quot;Janequot; gt; wrote in message
gt; ...
gt; gt; Hi Peo, I think I might not have been clear and/ or perhaps I did not
gt; gt; understand your response.
gt; gt; My formula is =vlookup($C20,'data!$A$1:$A$25',2,false). I have to look up
gt; gt; 24 colums of information from the data tab, starting in column 2. I
gt; gt; wanted
gt; gt; to know a fast way to copy that formula without having to manually change
gt; gt; the
gt; gt; column index number.
gt; gt;
gt; gt; I tried your solution without luck. I do appreciate any help on this.
gt; gt; Thanks! Jane
gt; gt;
gt; gt;
gt; gt; quot;Peo Sjoblomquot; wrote:
gt; gt;
gt; gt;gt; Replace 3 with
gt; gt;gt;
gt; gt;gt; COLUMNS($C:E)
gt; gt;gt;
gt; gt;gt; and copy across will return 3, 4, 5 and so on
gt; gt;gt;
gt; gt;gt; having said that if you use $A$1:$A$50 as a table you can only lookup one
gt; gt;gt; column so maybe that part was a typo
gt; gt;gt;
gt; gt;gt; --
gt; gt;gt; Regards,
gt; gt;gt;
gt; gt;gt; Peo Sjoblom
gt; gt;gt;
gt; gt;gt; Portland, Oregon
gt; gt;gt;
gt; gt;gt;
gt; gt;gt;
gt; gt;gt;
gt; gt;gt; quot;Janequot; gt; wrote in message
gt; gt;gt; ...
gt; gt;gt; gt;I have copied my vlookup formula across 25 columns and need to reference
gt; gt;gt; gt;25
gt; gt;gt; gt; columns on a data tab. My first formula is:
gt; gt;gt; gt; =vlookup($C20,'data$A$1:$a$50',2,false).... I need to changed the
gt; gt;gt; gt; column
gt; gt;gt; gt; lookup for the remaining 24 columns to =vlookup(.....,3,false)
gt; gt;gt; gt; =vlookup(.....,4,false) and so on.
gt; gt;gt; gt;
gt; gt;gt; gt; Is there a faster way to change the columns #'s rather than manually
gt; gt;gt; gt; changing them?
gt; gt;gt; gt;
gt; gt;gt; gt; thank you in advance! jane
gt; gt;gt;
gt; gt;gt;
gt;
gt;

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

quot;Janequot; wrote:

gt; Hi Peo, I think I might not have been clear and/ or perhaps I did not
gt; understand your response.
gt; My formula is =vlookup($C20,'data!$A$1:$A$25',2,false). I have to look up
gt; 24 colums of information from the data tab, starting in column 2. I wanted
gt; to know a fast way to copy that formula without having to manually change the
gt; column index number.
gt;
gt; I tried your solution without luck. I do appreciate any help on this.
gt; Thanks! Jane
gt;
gt;
gt; quot;Peo Sjoblomquot; wrote:
gt;
gt; gt; Replace 3 with
gt; gt;
gt; gt; COLUMNS($C:E)
gt; gt;
gt; gt; and copy across will return 3, 4, 5 and so on
gt; gt;
gt; gt; having said that if you use $A$1:$A$50 as a table you can only lookup one
gt; gt; column so maybe that part was a typo
gt; gt;
gt; gt; --
gt; gt; Regards,
gt; gt;
gt; gt; Peo Sjoblom
gt; gt;
gt; gt; Portland, Oregon
gt; gt;
gt; gt;
gt; gt;
gt; gt;
gt; gt; quot;Janequot; gt; wrote in message
gt; gt; ...
gt; gt; gt;I have copied my vlookup formula across 25 columns and need to reference 25
gt; gt; gt; columns on a data tab. My first formula is:
gt; gt; gt; =vlookup($C20,'data$A$1:$a$50',2,false).... I need to changed the column
gt; gt; gt; lookup for the remaining 24 columns to =vlookup(.....,3,false)
gt; gt; gt; =vlookup(.....,4,false) and so on.
gt; gt; gt;
gt; gt; gt; Is there a faster way to change the columns #'s rather than manually
gt; gt; gt; changing them?
gt; gt; gt;
gt; gt; gt; thank you in advance! jane
gt; gt;
gt; gt;