Vlookup problems

[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!

 

[brianwa]

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

 

[Guest]

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
(e-mail address removed)

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

 

[Max]

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

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

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

 

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



.

 

[Max]

You're welcome Michael!
Thanks for feedback.

Regards,
Max

 

[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