Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup using concatenated values - Excel 2000
I am copying data from a number of ranges (e.g. X1, X2 and X3) into a large
range named X_Data, which I then use for looking up data with the vlookup function. As the data in X_Data needs to be in alphanumeric order, I sort the data once I have copied and pasted the data into the range. The problem I am having is that the data in the first column is concatenated. I copy and use paste special/values into the X_Data range prior to sorting. When I copy and paste special the resulting data sometimes has an apostrophe in front. When this occurs the vlookup does not find the data, even if I go in and manually remove the apostrophe. I have tried to replicate this situation in another spreadsheet and have been unsuccessful. Any help would be appreciated. Craig |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup using concatenated values - Excel 2000
Compare the formating in the good worksheet with the bad worksheet and check
if the cells are formated as Text or General. You can verify the formating by clicking on the cell and then go to menu Format - Cell - Number and verifying which format is highlighted. The apostophe is to force the cell to be Text and you probably need a number. General format allows Excel to determine the correct format which sometimes causes problems like yours. "Craig A" wrote: I am copying data from a number of ranges (e.g. X1, X2 and X3) into a large range named X_Data, which I then use for looking up data with the vlookup function. As the data in X_Data needs to be in alphanumeric order, I sort the data once I have copied and pasted the data into the range. The problem I am having is that the data in the first column is concatenated. I copy and use paste special/values into the X_Data range prior to sorting. When I copy and paste special the resulting data sometimes has an apostrophe in front. When this occurs the vlookup does not find the data, even if I go in and manually remove the apostrophe. I have tried to replicate this situation in another spreadsheet and have been unsuccessful. Any help would be appreciated. Craig |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup using concatenated values - Excel 2000
Hi,
You can clean up the results after you do your paste special values command by selecting and empty cell and choosing copy, then select all the cells in the problem column and choose Edit, Paste Special, Add. -- If this helps, please click the Yes button Cheers, Shane Devenshire "Craig A" wrote: I am copying data from a number of ranges (e.g. X1, X2 and X3) into a large range named X_Data, which I then use for looking up data with the vlookup function. As the data in X_Data needs to be in alphanumeric order, I sort the data once I have copied and pasted the data into the range. The problem I am having is that the data in the first column is concatenated. I copy and use paste special/values into the X_Data range prior to sorting. When I copy and paste special the resulting data sometimes has an apostrophe in front. When this occurs the vlookup does not find the data, even if I go in and manually remove the apostrophe. I have tried to replicate this situation in another spreadsheet and have been unsuccessful. Any help would be appreciated. Craig |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup using concatenated column indexes | Excel Worksheet Functions | |||
Vlookup using a reference to a named range?(Excel 2000) | Excel Discussion (Misc queries) | |||
How do I convert values in figures to words in EXCEL 2000? | Excel Worksheet Functions | |||
#VALUE! error: vlookup works in Excel 2000 but not 2003 | Excel Discussion (Misc queries) | |||
How do I filter out duplicate values in Excel 2000? | Excel Worksheet Functions |