Archive of Mr Excel Message Board

Back to General Excel archive index
Back to archive home


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?


Is your workbook's calculation mode set to Manual? ...

Posted by Mark W. on January 16, 2002 1:44 PM
Check the Calculation tab via using the
Tools | Options... menu command. If it is Manual
set it to Automatic.

It is set to Automatic

Posted by Armando on January 16, 2002 2:02 PM
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.


Re: It is set to Automatic

Posted by Mark W. on January 16, 2002 2:12 PM
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?

Another possibility

Posted by Adam S. on January 16, 2002 2:16 PM

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

All you really need to do is...

Posted by Mark W. on January 16, 2002 2:28 PM
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.

Re: It is set to Automatic

Posted by Barrie Davidson on January 16, 2002 2:49 PM

Have a look at my response at


BarrieBarrie Davidson

This archive is from the original message board at
All contents © 1998-2004
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.