software

Hi,
If in a formula =vlookup(a1,D:F,3,false) someone later adds a column between
D amp; 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.

PaulIF 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

quot;Paulquot; gt; wrote in message
...
gt; Hi,
gt; If in a formula =vlookup(a1,D:F,3,false) someone later adds a column
gt; between
gt; D amp; F, the result will be incorrect. Is there anyway to make the formula
gt; resillient to columns being added to or subtracted from.
gt;
gt; Thanks for looking.
gt;
gt; Paul
gt;
Try this:
=VLOOKUP(A1,D:F,COLUMNS(D:F),false)

Does that help?

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

XL2002, WinXP-Proquot;Paulquot; wrote:

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

Are your headers unique?

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

You may want to read Debra Dalgleish's notes:
www.contextures.com/xlFunctions02.html (for =vlookup())
and
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:
gt;
gt; Hi,
gt; If in a formula =vlookup(a1,D:F,3,false) someone later adds a column between
gt; D amp; F, the result will be incorrect. Is there anyway to make the formula
gt; resillient to columns being added to or subtracted from.
gt;
gt; Thanks for looking.
gt;
gt; Paul

--

Dave Peterson

Thanks, that works fine and is dead simple.

Cheers

Paul

quot;Ron Coderrequot; wrote:

gt; Try this:
gt; =VLOOKUP(A1,D:F,COLUMNS(D:F),false)
gt;
gt; Does that help?
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;Paulquot; wrote:
gt;
gt; gt; Hi,
gt; gt; If in a formula =vlookup(a1,D:F,3,false) someone later adds a column between
gt; gt; D amp; F, the result will be incorrect. Is there anyway to make the formula
gt; gt; resillient to columns being added to or subtracted from.
gt; gt;
gt; gt; Thanks for looking.
gt; gt;
gt; gt; Paul
gt; gt;

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

quot;Dave Petersonquot; wrote:

gt; Are your headers unique?
gt;
gt; If yes, then maybe =index(match()) would work better.
gt;
gt; You may want to read Debra Dalgleish's notes:
gt; www.contextures.com/xlFunctions02.html (for =vlookup())
gt; and
gt; www.contextures.com/xlFunctions03.html (for =index(match()))
gt;
gt; =vlookup(a1,d:f,column(f:f)-column(d:d) 1,false)
gt;
gt; ps. I've always thought it made life much easier if my table was on a separate
gt; dedicated worksheet. Then I wouldn't have to worry about how inserting/deleting
gt; rows/columns outside my table would affect my table.
gt;
gt; =vlookup(a1,sheet2!d:f,column(sheet2!f:f)-column(sheet2!d:d) 1,false)
gt;
gt; If my table were on sheet2.
gt;
gt;
gt; Paul wrote:
gt; gt;
gt; gt; Hi,
gt; gt; If in a formula =vlookup(a1,D:F,3,false) someone later adds a column between
gt; gt; D amp; F, the result will be incorrect. Is there anyway to make the formula
gt; gt; resillient to columns being added to or subtracted from.
gt; gt;
gt; gt; Thanks for looking.
gt; gt;
gt; gt; Paul
gt;
gt; --
gt;
gt; Dave Peterson
gt;