Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookup Problem with Number format
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookup Problem with Number format
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) "Derek Upson - Pioneer" wrote in message ... 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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookup Problem with Number format
On Dec 26, 3:53*pm, "Derek Upson - Pioneer"
wrote: 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookup Problem with Number format
The reason the dates are left justified is because they are seen as text by
Excel not real dates -- Regards, Peo Sjoblom "Noob Jedi" wrote in message ... On Dec 26, 3:53 pm, "Derek Upson - Pioneer" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
general number format and VLOOKUP | Excel Worksheet Functions | |||
vlookup format problem | Excel Worksheet Functions | |||
Number Format Problem | Excel Discussion (Misc queries) | |||
Custom number format problem | Excel Worksheet Functions | |||
excel format cells/Number/Category: Number problem | Excel Discussion (Misc queries) |