VLOOKUP help needed.!!

[RickJB]

I have several sheets with tabels using VLOOKUP in adjecent collums.

eg.
=VLOOKUP($A3;'DATA'!$A$6:$DC$283;56) followed by
=VLOOKUP($A3;'DATA'!$A$6:$DC$283;59) etc.

Now I I need to insert extra collums in both the 'DATA', as well as the
VLOOKUP sheets.
However when I insert new collums in the 'DATA' sheets the collumn number in
the VLOOKUP formula stays the same.
I need the collum numbers in the formula to change with the new place in the
'DATA' sheet.

eg.
insert 3 new collums on collumn nr 45
=VLOOKUP($A3;'DATA'!$A$6:$DC$283;56) becomes
=VLOOKUP($A3;'DATA'!$A$6:$DC$283;59)
And =VLOOKUP($A3;'DATA'!$A$6:$DC$283;59) becomes
=VLOOKUP($A3;'DATA'!$A$6:$DC$283;62)

There are just too many to change manually..
Any way to make this work automatically?

 

[Pete_UK]

You can do Find & Replace (CTRL-H) - highlight the offending columns,
then CTRL-H and:

Find What: ;56
Replace With: ;59
Click Replace All

CTRL-H again, then:

Find What: ;59
Replace With: ;62
Click Replace All

Hope this helps.

Pete

 

[RickJB]

Thanks Pete,

But to do this one collumn at the time will take days and will be very hard
error-prone.
Is this the only way or is it maybe possible to do this for all collums at
the same time?
I mean when inserting new collumns the collumn numbers in the formula will
automatically add the number of inserted collums.

Any tips?

 

[Max]

You might consider using index/match

Eg for: > =VLOOKUP($A3;'DATA'!$A$6:$DC$283;56)
the equivalent index/match could be:
=INDEX(Data!BD:BD,MATCH($A3,Data!$A:$A,0))

Any subsequent insertion of new columns in sheet: Data
would not disrupt the formula's return