Vlookup - effect of adding columns

P

Paul

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
 
D

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))
 
R

Ron Coderre

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

Does that help?

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

XL2002, WinXP-Pro
 
D

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.
 
A

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
 
Top