Making vlookup aware of changes

[Terry Freedman]

Hi
Is it possible to make vlookup automatically aware of changes to the
structure of the data set being used? What I mean is this: at present,
if I delete a column in the data, the index part of the vlookup formula
remains the same, so it ends up referencing the worng column of data.
What I need is for it to realise that, because a column has been
deleted, it now needs to look in, say, column 2 rather than column 3.
I've tried using named references in the vlookup formula, but that
results in an error message. I'm sure there must be a solution, but I'm
stumped!

Thanks in advance for any help

Terry

 

[Bob Phillips]

You could try something like this

=VLOOKUP("c",J1:M6,COLUMN(M1)-COLUMN(J1)-1,FALSE)

but it seems fraught with danger to me, because if the column after your
target column is deleted, it will still look one column earlier.
Perhaps this approach will suit you better

=INDEX(L1:L6,MATCH("c",$J$1:$J$6,0))

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Terry Freedman]

Thanks, Bob
I couldn't get either of these to work properly tho. The solution I
have so far, which isn't very neat but it works, is to retain the
unwanted columns but delete the data in them, and change the index
number in the vlookup formula. But it would be much better if it was
all handled automaticaly I think.
Cheers

Terry

 

[Bob Phillips]

What did they do/not do that wasn't what was wanted.

Perhaps a worked through example would help?

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Terry Freedman]

I probably didn't use them properly or something. At the moment, the
formula is (eg):

=VLOOKUP($A$3,summdata,66)

where summdata is a huge table of data on another sheet

T

 

[aaron.kempf]

yeah.. do this

throw vlookup away.

keep your data in a database.

and do subqueries-- subqueries are a LOT more powerful than vlookup to
say the least