Column reference in Vlookup formula
 

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'?

Column reference in Vlookup formula
 

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

On Tue, 26 Aug 2008 12:01:05 -0700, Kevin K
wrote:

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'?