Vlookup - effect of adding columns

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
dguillett1@austin.rr.com
"Paul" <Paul@discussions.microsoft.com> wrote in message
news:8EFF3B66-F457-4753-AAA1-1439DCBA3FFB@microsoft.com...
> 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
>

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

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