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