VLOOKUP Problem

I have been using VLOOKUP functions extensively for a long time and recently
encountered a problem that I haven't seen before.

My VLOOKUP formula is: =VLOOKUP(B564,$B$1:$F$558,5,FALSE)

When I insert a new blank column in the middle of the array, the VLOOKUP
function continues to return the same value as it did before I inserted the
column. After inserting the column, the old value is now in the 6th column,
but the VLOOKUP output doesn't change.

The calculation option in the Tools-Options menu is set to "Automatic" and
pressing the F9 key doesn't change the output. The only way I am able to get
the VLOOKUP to change is by going into the VLOOKUP function and then pressing
enter.

A person I know recently told me that he also has this problem with the
VLOOKUP. Has anyone encountered this problem before? Any suggestions would
be appreciated.

Hi!

Are you always using the last column of the table as the
column_index_number? If so:

=VLOOKUP(B564,$B$1:$F$558,COLUMNS($B1:$F558),0)

Do your columns have headers? Use a MATCH function to find the correct
column_index_number:

=VLOOKUP(B564,$B$1:$F$558,MATCH("column_header",$B$1:$F$1,0),0)

Biff

"Ian" <[email protected]> wrote in message
news:[email protected]...
>I have been using VLOOKUP functions extensively for a long time and
>recently
> encountered a problem that I haven't seen before.
>
> My VLOOKUP formula is: =VLOOKUP(B564,$B$1:$F$558,5,FALSE)
>
> When I insert a new blank column in the middle of the array, the VLOOKUP
> function continues to return the same value as it did before I inserted
> the
> column. After inserting the column, the old value is now in the 6th
> column,
> but the VLOOKUP output doesn't change.
>
> The calculation option in the Tools-Options menu is set to "Automatic" and
> pressing the F9 key doesn't change the output. The only way I am able to
> get
> the VLOOKUP to change is by going into the VLOOKUP function and then
> pressing
> enter.
>
> A person I know recently told me that he also has this problem with the
> VLOOKUP. Has anyone encountered this problem before? Any suggestions
> would
> be appreciated.

Hi Biff,

Thanks for your reply. I have used the COLUMNS function in the past and you
are correct that it does indeed work in this situation. I had not used the
MATCH in this situation before and it is a good idea.

I guess my main concern is to understand why the initial formula I wrote
below doesn't work when I add a new column. Do you think that it's a glitch
of some sort with Excel? When I add a new column, I would expect the VLOOKUP
to change because after I add the column, the 5th column in my array has
become the 6th column, but as I descibed, the VLOOKUP continues to show the
same result as it had previously until you go into the formula and then hit
Enter.

Thanks again,

Ian



"Biff" wrote:

> Hi!
>
> Are you always using the last column of the table as the
> column_index_number? If so:
>
> =VLOOKUP(B564,$B$1:$F$558,COLUMNS($B1:$F558),0)
>
> Do your columns have headers? Use a MATCH function to find the correct
> column_index_number:
>
> =VLOOKUP(B564,$B$1:$F$558,MATCH("column_header",$B$1:$F$1,0),0)
>
> Biff
>
> "Ian" <[email protected]> wrote in message
> news:[email protected]...
> >I have been using VLOOKUP functions extensively for a long time and
> >recently
> > encountered a problem that I haven't seen before.
> >
> > My VLOOKUP formula is: =VLOOKUP(B564,$B$1:$F$558,5,FALSE)
> >
> > When I insert a new blank column in the middle of the array, the VLOOKUP
> > function continues to return the same value as it did before I inserted
> > the
> > column. After inserting the column, the old value is now in the 6th
> > column,
> > but the VLOOKUP output doesn't change.
> >
> > The calculation option in the Tools-Options menu is set to "Automatic" and
> > pressing the F9 key doesn't change the output. The only way I am able to
> > get
> > the VLOOKUP to change is by going into the VLOOKUP function and then
> > pressing
> > enter.
> >
> > A person I know recently told me that he also has this problem with the
> > VLOOKUP. Has anyone encountered this problem before? Any suggestions
> > would
> > be appreciated.
>
>
>

>Do you think that it's a glitch of some sort with Excel?

Not really a glitch, but probably not a "feature" that MS thought about.

You'll notice that a range or cell reference does change automatically as
you move things around. The column_index argument is a constant and MS
apparently didn't think about having that change with respect to the range
references.

Biff

"Ian" <[email protected]> wrote in message
news:[email protected]...
> Hi Biff,
>
> Thanks for your reply. I have used the COLUMNS function in the past and
> you
> are correct that it does indeed work in this situation. I had not used
> the
> MATCH in this situation before and it is a good idea.
>
> I guess my main concern is to understand why the initial formula I wrote
> below doesn't work when I add a new column. Do you think that it's a
> glitch
> of some sort with Excel? When I add a new column, I would expect the
> VLOOKUP
> to change because after I add the column, the 5th column in my array has
> become the 6th column, but as I descibed, the VLOOKUP continues to show
> the
> same result as it had previously until you go into the formula and then
> hit
> Enter.
>
> Thanks again,
>
> Ian
>
>
>
> "Biff" wrote:
>
>> Hi!
>>
>> Are you always using the last column of the table as the
>> column_index_number? If so:
>>
>> =VLOOKUP(B564,$B$1:$F$558,COLUMNS($B1:$F558),0)
>>
>> Do your columns have headers? Use a MATCH function to find the correct
>> column_index_number:
>>
>> =VLOOKUP(B564,$B$1:$F$558,MATCH("column_header",$B$1:$F$1,0),0)
>>
>> Biff
>>
>> "Ian" <[email protected]> wrote in message
>> news:[email protected]...
>> >I have been using VLOOKUP functions extensively for a long time and
>> >recently
>> > encountered a problem that I haven't seen before.
>> >
>> > My VLOOKUP formula is: =VLOOKUP(B564,$B$1:$F$558,5,FALSE)
>> >
>> > When I insert a new blank column in the middle of the array, the
>> > VLOOKUP
>> > function continues to return the same value as it did before I inserted
>> > the
>> > column. After inserting the column, the old value is now in the 6th
>> > column,
>> > but the VLOOKUP output doesn't change.
>> >
>> > The calculation option in the Tools-Options menu is set to "Automatic"
>> > and
>> > pressing the F9 key doesn't change the output. The only way I am able
>> > to
>> > get
>> > the VLOOKUP to change is by going into the VLOOKUP function and then
>> > pressing
>> > enter.
>> >
>> > A person I know recently told me that he also has this problem with the
>> > VLOOKUP. Has anyone encountered this problem before? Any suggestions
>> > would
>> > be appreciated.
>>
>>
>>