vlookup
Posted by Armando on January 16, 2002 1:34 PM
The formula that I am doing with vlookup works fine, however, the only way I can get it to work is I double click on each cell that is being looked up, and then I get the result.
Does anyone know why there is a problem (why I have to select the cell) or what I can do to fix it?
Thanks
Posted by Mark W. on January 16, 2002 1:44 PM
Is your workbook's calculation mode set to Manual? ...
Check the Calculation tab via using the
Tools | Options... menu command. If it is Manual
set it to Automatic.
Posted by Armando on January 16, 2002 2:02 PM
It is set to Automatic
Good Idea, but I checked and it was set to Automatic. I then clicked on "Calc Now" and nothing. Do you have any other good ideas.
Thanks
Posted by Mark W. on January 16, 2002 2:12 PM
Re: It is set to Automatic
What's displayed in the cells containing the
"uncalculated" VLOOKUP formulas? What's the
cell format of the cells? Were these cells
formatted as Text at the time these formulas
were entered or pasted?
Posted by Adam S. on January 16, 2002 2:16 PM
Another possibility
Hiya
When you say it's not working I assume you mean the formula is not seeing a match (resulting in #N/A)
I may have seen your situation before (or something very similar). I suspect you are looking at a formatting issue, something behind the scenes that is not allowing the vlookup to recognize the match. I occasionally see this with imported data from another app, but you may also just be looking at a type of corruption in the file.
Either way you may want to try one of the following:
1: Highlight the data, go to data\text to columns,tab delimiter. If the vlookups suddenly work, you probably had tab markings that saved over from the other app.
2: Go to another column, apply a General Format. Copy down a formula like =left(A2,len(A2)) This should strip out any bizarre formatting issues with that particular column. Try basing your vlookup on this new column, if it works you may wish to delete the offending column after a paste special\values.
Sidenote, if it is a corruption based issue, you may need to go to a fresh sheet or even a new file instead of simply inserting a blank column.
Good luck
Adam S.
Posted by Armando on January 16, 2002 2:22 PM
Yes
Yes, I did import it as text, becuase there were a couple that had zero in front of it. I will redo it and import it as a number and see what happens.
Thanks, I hope it works
Posted by Mark W. on January 16, 2002 2:28 PM
All you really need to do is...
Make sure that these cells aren't formatted as
Text (which I believe they're not) and use
the Data | Text to Columns... menu command to
re-parse them into numeric values.
Posted by Barrie Davidson on January 16, 2002 2:49 PM
Re: It is set to Automatic
