VLookup Problem with Number format

[Derek Upson - Pioneer]

i have a spreadsheet that is 10K lines long. the first column basically
contains numbers (7 digits) but some of the rows are actually alphanumeric.
i then did a vlookup to look up some data from another workbook. they all
came back invalid. However, if I go into a cell, put the cursor at the end
of the number, and then hit enter....the number becomes right justified and
the vlookup value is shown.

not sure what is wrong with the number format, but what can i do short of
going into each cell and hitting enter in order to get the vlookup to work
properly?

thanks in advance for the help!

 

[Bob Phillips]

Try this,

=VLOOKUP(--lookup_value, lookup_table, col_index,FALSE)

note the -- before the lookup value

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Noob Jedi]

i have a spreadsheet that is 10K lines long.  the first column basically
contains numbers (7 digits) but some of the rows are actually alphanumeric..
i then did a vlookup to look up some data from another workbook.  they all
came back invalid.  However, if I go into a cell, put the cursor at the end
of the number, and then hit enter....the number becomes right justified and
the vlookup value is shown.

not sure what is wrong with the number format, but what can i do short of
going into each cell and hitting enter in order to get the vlookup to work
properly?

thanks in advance for the help!

This may be a little unconventional, but I experienced the same thing
when it came to dates. Some dates read as 1/1/2007 and appeared on the
right side of the cell. Those were the justified dates. The
unjustified could've read the same way, but was shown on the left side
of the cell. Those were the unjustified cells.

So what did I do?

Sounds simple and even stupid, but it worked for me. I simply, copied
the column with the dates. Then in an empty cell, I did a paste
special, of "Values" AND don't forget to include selecting in the
"operations" section of the paste special, "Add." This will return it
as the number and all you have to do is select the column to change
the number format to Date. Now all dates are justified.

 

[Peo Sjoblom]

The reason the dates are left justified is because they are seen as text by
Excel not real dates


--


Regards,


Peo Sjoblom

i have a spreadsheet that is 10K lines long. the first column basically
contains numbers (7 digits) but some of the rows are actually
alphanumeric.
i then did a vlookup to look up some data from another workbook. they all
came back invalid. However, if I go into a cell, put the cursor at the end
of the number, and then hit enter....the number becomes right justified
and
the vlookup value is shown.

not sure what is wrong with the number format, but what can i do short of
going into each cell and hitting enter in order to get the vlookup to work
properly?

thanks in advance for the help!

This may be a little unconventional, but I experienced the same thing
when it came to dates. Some dates read as 1/1/2007 and appeared on the
right side of the cell. Those were the justified dates. The
unjustified could've read the same way, but was shown on the left side
of the cell. Those were the unjustified cells.

So what did I do?

Sounds simple and even stupid, but it worked for me. I simply, copied
the column with the dates. Then in an empty cell, I did a paste
special, of "Values" AND don't forget to include selecting in the
"operations" section of the paste special, "Add." This will return it
as the number and all you have to do is select the column to change
the number format to Date. Now all dates are justified.