Open a workbook with two sheets (sheet1 and sheet2).
In sheet1 cell B1, create a vlookup(A1,Sheet2!$C$1:$E$10,3,false).
Put in some values that work.
On sheet2, insert a column at B and move (the old column C) column D to the
new column B.
The vlookup changes to vlookup(A1,Sheet2!$B$1:$F$10,3,false).
The problem: The first column reference ($C$1) changes to ($B$1) as the old
column C was moved to B. The second column reference ($E$10) changed to
($F$10) due to the inserted column. But the offset (3) remained the same
instead of changing to (5) which would recognize the effect of inserting the
column and moving the source column.
Is there a fix for this?
Thank you,
SoCal Rick

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...et.f unctions

vlookup(A1,Sheet2!$C$1:$E$10,Columns(Sheet2!$C$1:$ E$10),false)

"SoCal Rick" wrote:

Open a workbook with two sheets (sheet1 and sheet2).
In sheet1 cell B1, create a vlookup(A1,Sheet2!$C$1:$E$10,3,false).
Put in some values that work.
On sheet2, insert a column at B and move (the old column C) column D to the
new column B.
The vlookup changes to vlookup(A1,Sheet2!$B$1:$F$10,3,false).
The problem: The first column reference ($C$1) changes to ($B$1) as the old
column C was moved to B. The second column reference ($E$10) changed to
($F$10) due to the inserted column. But the offset (3) remained the same
instead of changing to (5) which would recognize the effect of inserting the
column and moving the source column.
Is there a fix for this?
Thank you,
SoCal Rick

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...et.f unctions