Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup lookup_value
I've got a very simple Vlookup function that is giving me a problem. I am
trying to find a match to a number, but can only get the match to work if I enter an apostrophe (') before the number (found in C22) and make it a text. I've checked that both the source cell and the searched cells are formatted to identical number format. =VLOOKUP(C22,Prices,2,FALSE) "Prices" is a sheet in a second externally linked workbook. Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup lookup_value
I'm guessing that the data in your Prices range is stored as Text. The cells
may be formatted as a Number, but that doesn't necessarily mean that the data is a Number. Changing a cell format from Text to Number doesn't affect existing data. Try this: In your Prices worksheet, enter a 1 into any blank cell. Copy that cell Select your range of data to convert from text to numbers From the Edit Menu, choose "Paste Special..." Select the options for 'Values' and 'Multiply' Click OK Delete the 1 you entered originally Now, any text values in that range should be converted to numbers. HTH, Elkar "ATIHelp" wrote: I've got a very simple Vlookup function that is giving me a problem. I am trying to find a match to a number, but can only get the match to work if I enter an apostrophe (') before the number (found in C22) and make it a text. I've checked that both the source cell and the searched cells are formatted to identical number format. =VLOOKUP(C22,Prices,2,FALSE) "Prices" is a sheet in a second externally linked workbook. Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup lookup_value
Sounds like Prices key column is text. Fix that or use
=VLOOKUP(TEXT(C22,"@"),Prices,2,FALSE) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "ATIHelp" wrote in message ... I've got a very simple Vlookup function that is giving me a problem. I am trying to find a match to a number, but can only get the match to work if I enter an apostrophe (') before the number (found in C22) and make it a text. I've checked that both the source cell and the searched cells are formatted to identical number format. =VLOOKUP(C22,Prices,2,FALSE) "Prices" is a sheet in a second externally linked workbook. Thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup lookup_value
Thanks,
Your answer set off a light bulb on some other things that had been bothering me. I thought changing cell formats changed the data format. I made the changes properly and things look good now. "Elkar" wrote: I'm guessing that the data in your Prices range is stored as Text. The cells may be formatted as a Number, but that doesn't necessarily mean that the data is a Number. Changing a cell format from Text to Number doesn't affect existing data. Try this: In your Prices worksheet, enter a 1 into any blank cell. Copy that cell Select your range of data to convert from text to numbers From the Edit Menu, choose "Paste Special..." Select the options for 'Values' and 'Multiply' Click OK Delete the 1 you entered originally Now, any text values in that range should be converted to numbers. HTH, Elkar "ATIHelp" wrote: I've got a very simple Vlookup function that is giving me a problem. I am trying to find a match to a number, but can only get the match to work if I enter an apostrophe (') before the number (found in C22) and make it a text. I've checked that both the source cell and the searched cells are formatted to identical number format. =VLOOKUP(C22,Prices,2,FALSE) "Prices" is a sheet in a second externally linked workbook. Thanks |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup lookup_value
Thanks Bob,
Using the "@" in the text function is new to me. Definitely something I can use in other places. "Bob Phillips" wrote: Sounds like Prices key column is text. Fix that or use =VLOOKUP(TEXT(C22,"@"),Prices,2,FALSE) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "ATIHelp" wrote in message ... I've got a very simple Vlookup function that is giving me a problem. I am trying to find a match to a number, but can only get the match to work if I enter an apostrophe (') before the number (found in C22) and make it a text. I've checked that both the source cell and the searched cells are formatted to identical number format. =VLOOKUP(C22,Prices,2,FALSE) "Prices" is a sheet in a second externally linked workbook. Thanks |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup lookup_value
"Bob Phillips" wrote...
Sounds like Prices key column is text. Fix that or use =VLOOKUP(TEXT(C22,"@"),Prices,2,FALSE) .... Or replace TEXT(C22,"@") with C22&"". |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup(lookup_value) | Excel Worksheet Functions | |||
multiple lookup_value | Excel Discussion (Misc queries) | |||
VLOOKUP(lookup_value, ___ ,col_index_num,range_lookup) | Excel Worksheet Functions | |||
vlookup:same lookup_value, different returns | Excel Worksheet Functions | |||
VLOOKUP - dealing with lookup_value differences | Excel Worksheet Functions |