Moving columns with VLOOKUP formulas
 

I have a large spreadsheet that has a lot of VLOOKUPs in it. I want to add a
column somewhere in the worksheet (let's say for example after Column C).
The problem is that when I do that all the formulas after that column don't
seem to move with the column. What I mean is that if I had a VLOOKUP in
column C looking up to Column 10 in another spread sheet and Column D is
looking up to 11 then when I insert a new column I want D to now look up 10
and E to look up 11 but it doesn't seem to work. This makes it very hard to
add to a spreadsheet once it's created. Any suggestions?

In regards to what I wrote below, my biggest problem comes when I want to add
a column on the master spreadsheet. The other worksheets that are looking
back to this one are now all incorrect. In the past I've gone in and
manually updated each column after but their has to be a better way.

"Brad" wrote:

I have a large spreadsheet that has a lot of VLOOKUPs in it. I want to add a
column somewhere in the worksheet (let's say for example after Column C).
The problem is that when I do that all the formulas after that column don't
seem to move with the column. What I mean is that if I had a VLOOKUP in
column C looking up to Column 10 in another spread sheet and Column D is
looking up to 11 then when I insert a new column I want D to now look up 10
and E to look up 11 but it doesn't seem to work. This makes it very hard to
add to a spreadsheet once it's created. Any suggestions?

Brad wrote:
In regards to what I wrote below, my biggest problem comes when I want to add
a column on the master spreadsheet. The other worksheets that are looking
back to this one are now all incorrect. In the past I've gone in and
manually updated each column after but their has to be a better way.

"Brad" wrote:


I have a large spreadsheet that has a lot of VLOOKUPs in it. I want to add a
column somewhere in the worksheet (let's say for example after Column C).
The problem is that when I do that all the formulas after that column don't
seem to move with the column. What I mean is that if I had a VLOOKUP in
column C looking up to Column 10 in another spread sheet and Column D is
looking up to 11 then when I insert a new column I want D to now look up 10
and E to look up 11 but it doesn't seem to work. This makes it very hard to
add to a spreadsheet once it's created. Any suggestions?

You might try something like
=VLOOKUP(1000,MasterSheet!A1:E10,COLUMN(MasterShee t!D1))

Alan Beban

If you have titles in your table, you could use a combination of index/match.

Debra Dalgleish has some nice instructions at:
http://www.contextures.com/xlFunctions03.html

Brad wrote:

I have a large spreadsheet that has a lot of VLOOKUPs in it. I want to add a
column somewhere in the worksheet (let's say for example after Column C).
The problem is that when I do that all the formulas after that column don't
seem to move with the column. What I mean is that if I had a VLOOKUP in
column C looking up to Column 10 in another spread sheet and Column D is
looking up to 11 then when I insert a new column I want D to now look up 10
and E to look up 11 but it doesn't seem to work. This makes it very hard to
add to a spreadsheet once it's created. Any suggestions?

--

Dave Peterson