I think that you want

=VLOOKUP(E1,Sheet2!$E$2:$J$13,COLUMN(Sheet2!F2)-COLUMN(Sheet2!$F$2)+2,FALSE)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Roger Govier" wrote in message
...
Hi Barbara

In any formula you can either add to or subtract from column number so
COLUMN()+4 where column =2 would refer to an offset of 6 from the value
found in VLOOKUP, rather than an offset of 2.

The originla formula given to you by Bob to place in Sheet 1 was
B1: =VLOOKUP(A1,Sheet2!$A$1:$F$100,Column(),False)

If you have inserted 4 columns on Sheet 1 then the formula will now be in
F1
and would read
=VLOOKUP(E1,Sheet2!$A$1:$F$100,Column(),False)
Because nothing has altered on Sheet2, then it will be reading values 4
columns further over on Sheet2 than it should, so in your case we need to
subtract from COLUMN().

If I have understood you correctly, then I believe the formula should now
be
F1: =VLOOKUP(E1,Sheet2!$A$1:$F$100,Column()-4,False)

--
Regards
Roger Govier
"Barbara" wrote in message
...
Ok, what if I change a couple of things.
I need to add in four columns (a new A thru D) on Sheet 1
And the info I am now getting on Sheet 2 is in A2 through I13

So the formula is in cell F2 thru M2 in Sheet 1(and copied down)
And it is reading from the data entered into column E in sheet 1 (E in
sheet
one searchs column A in sheet 2 to get the data and bring back to sheet
1
and
put it in columns F thru L )

Since Column reads A=1, B=2 and so on, is there a way to change it to be
column F thru L.

I hope I am making since.
Barbara
"Roger Govier" wrote:

Hi Barbara
Column() returns the column number A=1 B=2 etc.
In your formula, the use of COLUMN() is automatically stepping up the
offset
argument in the Vlookup expression to choose the appropriate value from
the
reference table.

--
Regards
Roger Govier
"Barbara" wrote in message
...
Yes the VLookup is working nicely. Thanks
One question though, I understand what everything is doing in the
formula
except Column()
Is that giving it a variable for whatever is in the column to copy
over?
Barbara