Error in VLOOKUP REFERENCE?

[Takeadoe]

The following formula is in a cell in a worksheet called MODEL. It
pulls data from the named range HarvestData. Problem is, if I add a
column before "M" in the named range HarvestData, effectively moving
data from M to N, my lookup formula obviously no longer works. Does
HarvestData need to be a dynamic range (it is not) or does the problem
lie in the VLOOKUP? Somehow I need to update the column reference in
the VLOOKUP when a column is added or subtracted in HarvestData.

Help on this would be greatly appreciated.

Mike


(VLOOKUP($A17,HarvestData,COLUMN(M14),0)

 

[Dave Peterson]

If you have headers for your columns, you may want to dump the =vlookup() and
use =index(match(),match()).

You can match on column 1 of the table (or any other column) and match on row 1
of the table, too.

Debra Dalgleish explains it:
http://www.contextures.com/xlFunctions03.html#IndexMatch2
http://www.contextures.com/xlFunctions03.html#IndexMatch3

 

[Takeadoe]

Thank you Dave! Before I move in that direction, I just want to make
sure I simply can't replace my absolute reference to the column in
HarvestData to a relative one? IOW, is there a way to have the column
updated in the formula to reflect the additional column added in
HarvestData?

Mike

 

[Dave Peterson]

COLUMN(M14)
will refer to the sheet with the cell with the formula.

Maybe you should just refer to the same sheet that has owns Harvestdata:

=VLOOKUP($A17,HarvestData,column('sheet2'!m:m),0)