Vlookup problems

M

Michael Milmoe

I have Vlookups set up for a particular Spreadsheet. Each
month I just drop the new data into the first column.
Even though the number is formatted just like the previous
month (i.e general, text, whatever) my vlookup formula
shows #N/A. If i double click in the data cell and hit
enter, the vlookup formula works. can you tell me why, or
if there is a better way than clicking and hitting enter?

Thanks!
 
B

brianwa

do you have your automatic calculation turned on?
Under tools<options<calculation.
 
M

Mark Graesser

It sounds like the data you are "dropping" in was formatted as text and then reformatted as a number. The actual data isn't converted into a number until you act on it in some way, like clicking in the cell and hitting enter

If your lookup array uses numbers, a number formatted as text is not a match

Good Luck
Mark Graesse
[email protected]

----- Michael Milmoe wrote: ----

I have Vlookups set up for a particular Spreadsheet. Each
month I just drop the new data into the first column.
Even though the number is formatted just like the previous
month (i.e general, text, whatever) my vlookup formula
shows #N/A. If i double click in the data cell and hit
enter, the vlookup formula works. can you tell me why, or
if there is a better way than clicking and hitting enter

Thanks
 
M

Max

Try using VALUE() to wrap around the lookup value,

instead of : =VLOOKUP(A1,TABLE,2,FALSE)

use: =VLOOKUP(VALUE(A1),TABLE,2,FALSE)
 
M

Michael Milmoe

Thanks Max! It worked.
-----Original Message-----
Try using VALUE() to wrap around the lookup value,

instead of : =VLOOKUP(A1,TABLE,2,FALSE)

use: =VLOOKUP(VALUE(A1),TABLE,2,FALSE)

--
hth
Max
-----------------------------------------
Please reply in thread

Use xdemechanik <at>yahoo<dot>com for email
---------------------------------------------------------- ------------------



.
Click to expand...
 
K

Ken Wright

Another option is to use Dave McRitchie's Trimall macro every time you dump data
into the sheet - This will clean up any garbage. You can then copy an empty
cell, select the entire range and do Edit / Paste Special / Add. This will
coerce any data that should be numeric back to numbers:-

http://www.mvps.org/dmcritchie/excel/join.htm#trimall
 
Top