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

"Dave Peterson" wrote:

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.


Paul wrote:

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

--

Dave Peterson