Error in VLOOKUP REFERENCE?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Takeadoe - 25 Sep 2006 14:10 GMT

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)

Reply to this Message

Dave Peterson - 25 Sep 2006 15:35 GMT

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

> 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
[quoted text clipped - 9 lines]
>
> (VLOOKUP($A17,HarvestData,COLUMN(M14),0)

Signature

Dave Peterson

Reply to this Message

Takeadoe - 25 Sep 2006 16:24 GMT

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

> If you have headers for your columns, you may want to dump the =vlookup() and
> use =index(match(),match()).
[quoted text clipped - 19 lines]
> >
> > (VLOOKUP($A17,HarvestData,COLUMN(M14),0)

Reply to this Message

Dave Peterson - 25 Sep 2006 16:46 GMT

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)

> 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
[quoted text clipped - 31 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message