Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vlookup problem | Excel Discussion (Misc queries) | |||
Vlookup problem with Date Time | Excel Worksheet Functions | |||
vlookup Problem | Excel Discussion (Misc queries) | |||
VLOOKUP Problem | Excel Worksheet Functions | |||
VLOOKUP, OFFSET, MATCH PROBLEM, HELP? | Excel Worksheet Functions |