Move cols by 1 in every formula on the whole workbook

[szakharov7723]

So I have database using lot's of VLOOKUP, but they all refer to the same array ,where new column needs to be inserted in the middle of array. The array in formula moves, but columns remain the same. Hence it pulls data from the wrong place now.
Is there a way to basically add 1 to every col in multiple VLOOKUP functions ?

 

[Special-K99]

You might have to do it manually as some columns may be referenced BEFORE the inserted column and some AFTER, e.g.

VLOOKUP(A1,B1:Z1, 2)
VLOOKUP(A1,B1:Z1,17)

A new column is inserted at column 13

Column 17 should be increased but column 2 should remain the same.

I can't see a way of doing this automatically.

 

[shg]

If you enter the formula as

=VLOOKUP(A1, B1:Z100, COLUMNS($B$1:$R$1))

(where R is the initial lookup column), it will adapt to inserted and deleted columns.