#1   Report Post  
Posted to microsoft.public.excel.misc
Ian
 
Posts: n/a
Default 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.
  #2   Report Post  
Posted to microsoft.public.excel.misc
Biff
 
Posts: n/a
Default VLOOKUP Problem

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



  #3   Report Post  
Posted to microsoft.public.excel.misc
Ian
 
Posts: n/a
Default VLOOKUP Problem

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




  #4   Report Post  
Posted to microsoft.public.excel.misc
Biff
 
Posts: n/a
Default VLOOKUP Problem

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






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
vlookup problem puiuluipui Excel Discussion (Misc queries) 2 February 5th 06 06:46 PM
Vlookup problem with Date Time normajmarsh Excel Worksheet Functions 0 February 3rd 06 08:33 PM
vlookup Problem marksuza Excel Discussion (Misc queries) 3 December 22nd 05 04:40 PM
VLOOKUP Problem Tosca Excel Worksheet Functions 7 July 23rd 05 10:43 PM
VLOOKUP, OFFSET, MATCH PROBLEM, HELP? Steve Excel Worksheet Functions 0 January 30th 05 10:11 PM


All times are GMT +1. The time now is 10:15 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"