Vlookup - effect of adding columns

[Guest]

Hi,
If in a formula =vlookup(a1,D:F,3,false) someone later adds a column between
D & F, the result will be incorrect. Is there anyway to make the formula
resillient to columns being added to or subtracted from.

Thanks for looking.

Paul

 

[Don Guillett]

IF the all coumns including the inserted column would always have someting
on row 1 then
=VLOOKUP(1,B:E,COUNT(B1:E1))

 

[Guest]

Try this:
=VLOOKUP(A1,D:F,COLUMNS(D:F),false)

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro

 

[Dave Peterson]

Are your headers unique?

If yes, then maybe =index(match()) would work better.

You may want to read Debra Dalgleish's notes:
http://www.contextures.com/xlFunctions02.html (for =vlookup())
and
http://www.contextures.com/xlFunctions03.html (for =index(match()))

=vlookup(a1,d:f,column(f:f)-column(d:d)+1,false)

ps. I've always thought it made life much easier if my table was on a separate
dedicated worksheet. Then I wouldn't have to worry about how inserting/deleting
rows/columns outside my table would affect my table.

=vlookup(a1,sheet2!d:f,column(sheet2!f:f)-column(sheet2!d:d)+1,false)

If my table were on sheet2.

 

[Guest]

Thanks, that works fine and is dead simple.

Cheers

Paul

 

[alex]

hello,

if i delete column A on second sheet and then insert column and put the data
back in place - is there a way to keep the range in vlookup constant (it
keeps changing to B: ...)

alex