Column reference in Vlookup formula

[Kevin K]

There are time when I need to add column to the section of the spreadsheet
that I'm referencing in my vlookup formula. When I add a column, the vlookup
formula adjusts the range but it doesn't change (i.e. increment) the existing
column references so all of the data retrieved is bad because it's now
referencing the wrong column. Is there a way to fix this without doing a
massive 'find and replace'?

 

[Gord Dibben]

Example only, your ranges may vary but by using the column() you can drag
across.

=VLOOKUP($G1,$A$2:$F$31,COLUMN()-6,FALSE) entered in F1


Gord Dibben MS Excel MVP