 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / September 2007vlookup -- converting numbers to text
I have a report (worksheet) that stores my "item numbers" that look like numbers as numbers. I have another report (worksheet) that stores all item numbers as text. I am trying to do a vlookup from the first report to the second report but I am having problems converting the number to text. For example, if the item number is 1202.01, I am trying to do the following: vlookup(text(B2,"0.00"),Lookup,2,false) where B2=1202.01 stored as a number. "Lookup" is a named range where the first column is the item number which is stored as text. This is returning "#N/A". What am I doing wrong? Thanks in advance!! :) Janet Why are you using TEXT. Why not use just vlookup(B2,Lookup,2,false) By the way, many people avoid range names that have other meanings (LOOKUP is a function name) best wishes  SignatureBernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email >I have a report (worksheet) that stores my "item numbers" that look like > numbers as numbers. [quoted text clipped - 20 lines] > > Janet > Why are you using TEXT. Why not use just > vlookup(B2,Lookup,2,false) I will change named range to "Items" instead of Lookup. The reason I do not use vlookup(B2,Items,2,false) is because: B2 is 1202.01 stored as a number. "Items" has the item number stored as text. vlookup(B2,Items,2,false) will result in "#N/A" due to type mismatch. > By the way, many people avoid range names that have other meanings (LOOKUP > is a function name) I have changed my naming convention. :) Thanks > best wishes > >I have a report (worksheet) that stores my "item numbers" that look like [quoted text clipped - 21 lines] > > > > Janet In this reply, I am changing the named range from "Lookup" to "Items". I think I've found one of the problems here. The "Items" ranges stores the number as text by putting the single quote (') in front of the number. I'll play with this and see up with which I can come. Any suggestions are welcome. Janet :) > I have a report (worksheet) that stores my "item numbers" that look like > numbers as numbers. [quoted text clipped - 18 lines] > > Janet Well, in putzing around looking for a solution, I tripped upon the following: vlookup(concatenate(b2),Items,2,false) This WORKED! It's kludgy but it works. I'm going to use this until I find another solution or it stops working... lol.. Thanks! Janet > In this reply, I am changing the named range from "Lookup" to "Items". > [quoted text clipped - 29 lines] > > > > Janet You could use: =vlookup(b2&"",Items,2,false) or if you have to match 1234.1 with 1234.10, you may want: =vlookup(text(b2,"0000.00"),Items,2,false) If you wanted to convert B2 to a number, you could use: =vlookup(--b2, .... The first minus will make the text number a number (but the opposite sign). The second minus will change the sign back. > Well, in putzing around looking for a solution, I tripped upon the following: > [quoted text clipped - 40 lines] > > > > > > Janet SignatureDave Peterson |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.