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

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

--
Don Guillett
SalesAid Software

"Paul" wrote in message
...
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

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

Does that help?

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

XL2002, WinXP-Pro


"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

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

Thanks, that works fine and is dead simple.

Cheers

Paul

"Ron Coderre" wrote:

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

Does that help?

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

XL2002, WinXP-Pro


"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

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