make it text in vlookup

I have a vlookup table like so:

234,     112
911,     999
260/1,  888
9W2,    777
..
etc..

The lookup works fine when there is text, ie. if I pass in "9W2", it returns 
777 as expected. It does not work where there are only numbers eg. 234, it 
doesn't recognise that it should return 112.

I have tried to make it treat it as text but nothing works.


0
Utf
3/22/2010 10:37:01 PM
excel.programming 6508 articles. 2 followers. Follow

1 Replies
804 Views

Similar Articles

[PageSpeed] 40

Lets see your formula.  Vlookup doesn't care if it is text or a number. 
Using your example it works fine for me... I am guessing the lookup value 
does not match but may seem to look the same.

Try something like =A1=C1 for a True or False, where A1 is the 234 lookup 
value and C1 is the 234 in the table.

HTH
Regards,
Howard

"Diana" <Diana@discussions.microsoft.com> wrote in message 
news:3633F92C-09D1-4269-9875-F19E574BF108@microsoft.com...
>I have a vlookup table like so:
>
> 234,     112
> 911,     999
> 260/1,  888
> 9W2,    777
> .
> etc..
>
> The lookup works fine when there is text, ie. if I pass in "9W2", it 
> returns
> 777 as expected. It does not work where there are only numbers eg. 234, it
> doesn't recognise that it should return 112.
>
> I have tried to make it treat it as text but nothing works.
>
> 


0
L
3/23/2010 12:02:23 AM
Reply:

Similar Artilces:

how do i count text cells in excel based on variable criteria?
I need a formula that will recalculate when the variables are changed. For example, I will input the variables for date "1/26/2010" and BU "09" and I want a count of how many assets that meet those criterion. For Acctg Date: 1/26/2010 BU: 09 Asset Count:_____ BU Acctg Date 08 1/25/2010 06 1/26/2010 06 1/25/2010 09 1/25/2010 09 1/26/2010 09 1/26/2010 Assuming in your example the date is in B1, the BU is in B2 and your data is in A5 to B10, enter this formula =SUMPRODUCT((A5:A10=B2)*(B5:B10=B1)) "anmaka57" wrote: > I need a for...

If text exists
Hi All, Not been around for a while so not even sure how to ask this... I have a spreadsheet consisting of 3 columns, column 1 is text, col 2 is a pseudo date, Like (aug 23) no year shown. colum 3 is a numeric value which can be either + or - shown as 12.35+ or as a negative 345.82- What I need to do is scan column 1 for a certain word, if it is there I need to copy column 3 into column 5 so that I can calculate the total values associated with the string. There is a possibility that there can be anything from 400 - 5000 rows in the sheet. I know it can be done, I just don't kno...

Scroll bar not showing in a text box on a continuous form
Hi I'm using Access XP and have a continuos form (which is a subfrom) which contains three text boxes. Each text box has its scroll bars property set to vertical yet they don't show. I tried using the on enter event to programatically set them using something like "myControl.scrollbars=2" but still they don't show and the code doesn't generate any error. This is a pain as the user doesn't know if there's more text than what's displayed. I'd like to avoid setting the height of the text box to show an extra half line so it's obvious as this...

How can I allow other users to make changes in a shared workbook
I have everything set up properly and other users cannot make and save changes in a shared workbook. Any suggestions? What happens when they try to make changes? If the worksheet is protected (and that's what is stopping them, unshare the workbook, unprotect the worksheet, unlock the cells that should be able to be changed, reprotect the worksheet, reshare the workbook and try it again.) What happens when they try to save? (I don't have a guess here.) yourdisplayname@discussions.microsoft.co wrote: > > I have everything set up properly and other users cannot make and save...

Visio 2003
Visio 4, 5, 2000 and 2002 could automagically create great drawings (flow-charts) from text-files. Visio 2003 professional does not recognize the text-files my VC6 application creates :-( I there an available another not so hard technique my application can use to let Visio create my simple drawings? ; ex2_visio_import.csv Template, "Audit Diagram.vst" Shape,"1","Terminator","Start",,,,,,, Shape,"2","I/O","Some kind of input",,,,,,, Shape,"3","Decision","Test the input",,,,,,, Shape,"4&q...

Data from .txt file being read in as date, and should be text
I have a macro that reads in data from a .txt file. The data contains user IDs that consist of the first 4 letters of their name and 4 numbers. When names are read in that begin with MARCxxxx (ie MARC1234), excel is formating them as a date, and I lose the original data (even when I try to format them after the import, they are a number and the letters are not there. I've tried to format the column that they data read into before the import, but excel still imports them as a date. How can I make the import be read as text? Here is the code for the section I am reading in data for. Do Whi...

Specific Text Box
I have an BeforeUpdate HandleError msg that pops up when a box that is required has been left Blank, in the same code could I place a goto function for that text box that needs input. Use the SetFocus method -- Dave Hargis, Microsoft Access MVP "phuser" wrote: > I have an BeforeUpdate HandleError msg that pops up when a box that is > required has been left Blank, in the same code could I place a goto function > for that text box that needs input. > > > ...

Text On Forms Becomes Blurry/Bold
Hi everyone, I have an interesting problem that I can't find a solution to. Once a form has focus for a few seconds, some of the text becomes blurry and slightly bold. There is no code on the form that would cause this to happen. I was wondering if anyone has seen a bug like this before. This only happens on some computers that use the database. I am using Office 2003. See these images: http://www.kslab.com/normal.jpg http://www.kslab.com/blurry.jpg Thanks in advance, Joshua Joshua Did you check at the Search sites? I've never heard of this happening before. Well, actua...

Display text with Unicode format (ex: Chinese) on controls written by VC6.0
Hi all, We can write a project with Debug-Unicode mode and display text with Unicode format? Ex: Chinese or Japanese Idea? Thanks. >We can write a project with Debug-Unicode mode and display text with Unicode >format? Ex: Chinese or Japanese With VC6 you can build a debug (or release) Unicode project that when run on a proper Unicode platform (like XP) should be able to display Chinese or Japanese texts (providing the font you're using has the characters). Dave Tittle bar, menu, button can display text with Unicode format when i build a debug Unicode project? We can use fo...

VLOOKUP driving me crazy
Hi all - I have a VLOOKUP formula that is really bugging me. Instead o reading accross it seems to be reading down. The formular is =VLOOKUP(A1,OCU_VCU!W1:X20,1) I cant see whats wrong with it - ive used VLOOKUP so much in othe projects - Im either really missing something or excel has gone funny. Thanks for any comments -- LB7 ----------------------------------------------------------------------- LB79's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1215 View this thread: http://www.excelforum.com/showthread.php?threadid=49305 You aren�t giving a huge am...

Hyperlink
I've ceated a page in P2003 which containd graphics to which I've attached hyperlinks to go to specific locations within our company website. When hovering over the graphic the whole path is displayed. Is there a way of only displaying text that I want to display? Inside the Insert Hyperlink option, the "TExt to Display" option is greyed out! Thanks I have never been able to get the "text to display" option either. You can add alternative text to an image by selecting the image > format > picture > web tab. The alt text will be visible if the image ...

converting line-delimited text into cells
I have a text file with approximately 1000 lines of data, each line is 100 chars or less. The first 15 lines are column headers (some columns are unused), then every 12 subsequent lines is one record. What is the easiest way to get each record to appear in Excel (2007) on a single line, one field per column? Thank you If desired, send your file to my address below. I will only look if: 1. You send a copy of this message on an inserted sheet 2. You give me the newsgroup and the subject line 3. You send a clear explanation of what you want ...

Changing Text to Number Problem.
I have a problem when importing information into an Excel spreadsheet. We have a system at work that has a front end Web based application (asp) that accesses an SQL database. Not all of the software is written yet, so to sort/filter data I have to copy lists of information from the Web based application to an Excel spreadsheet. Much of the information that I import (copy and paste) into the spreadsheet are hyperlinks to other information in the SQL database. To get rid of the hyperlinks once I have the list copied to my spreadsheet, I (copy and paste special, values only) the information on...

In HQ make works sheets formulas go cross tables..
It would be great to have more functionality in the HQ worksheet formula's if you add a supplier and they have a lot of items, but the costs are the same, a lot of time could be saved if you could copy the cost from the standard cost. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in...

help on macro for making chart
Hi, i want to make a plot using macro where I want my x-axis to be the value from the same column but random rows for example R18C3,R19C3,R21C3,R23C3,R25C3,R27C3 ( only rows are changing) and my Y-axis should also be something like this R18C5,R19C5,R21C5,R23C5,R25C5,R27C5 ( only rows are changing) For one plot It is working but i do not know how i can plot for other columns... the code looks like this Sub Macro2() ' ' Macro2 Macro ' Macro recorded 3/30/2007 by Roger ' ' Keyboard Shortcut: Ctrl+m ' Charts.Add ActiveChart.ChartType = xlXYScatterLines ...

how do i get rid of the text box outline around my text?
I hope you're filling your text boxes with more text than you're filling your messages :o) And you just need to read the help files, read the manuals, and look for a button marked Line Color. Set it to No Line. -- Ed Bennett - MVP Microsoft Publisher http://www.mvps.org/the_nerd/ Before reading this message, view the disclaimer: http://mvps.org/the_nerd/disclaim.htm > "Ed Bennett" <the_nerd@mvps.org> wrote in message > news:%23bdlu9isEHA.3460@TK2MSFTNGP15.phx.gbl... > > I hope you're filling your text boxes with more text > than you'r...

How do I wrap text in the subject column in the list of tasks
i seeing the list of taks in using the menu "Tasks" in outlook. However I am unable to wrap the text under the subject column that I miss the words. Even when printed in landscape the words are truncated. So how do i wrap the text in the view and print out, so that I can see all the text. You can't, you can either remove and/or resize some columns or select the entire list and paste it into Excel and modify it from there. -- Robert Sparnaaij [MVP-Outlook] Coauthor, Configuring Microsoft Outlook 2003 http://www.howto-outlook.com/ Outlook FAQ, HowTo, Downloads,...

Vlookup with a specific item
Hi, I would like to vlookup on employees' degree start date, eg.: A B C emp. no. Start date Edu 1 01/01/90 Dip 1 01/01/95 Deg 2 01/01/82 Cert 2 01/01/86 Dip 2 01/01/90 Deg How do I vlookup on the emp. no and deg start date? Thanks in advance. Please note that this is an array formula. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can notice ...

Disconnect frames in publisher, keep text in disconnected frames
Publisher 2000. A 125 page story. text in connected frames with many associated pictures. If add text to early pages (a family history), the pictures for a 100 + pages are no longer aligned. Would like to disconnect frames at appropriate intervals to minimise disruption, keeping text in current frames. At present it appears that text goes into 'overflow area' if frames disconnected. Can I , altenatively copy all the text and re-start somehow? Save the Publisher document in rich text format, open it in Word and do your editing, again saving in Rich Text. In Publisher, open the Wo...

Vlookup in VBA
I have to do a vlookup with VBA in one worksheet in which the Table_Array will go from column A11 to L11 down to a row, containing a specific text, "Paid." is that possible Hi please stay in your existing thread -- Regards Frank Kabel Frankfurt, Germany Jeff wrote: > I have to do a vlookup with VBA in one worksheet in which the > Table_Array will go from column A11 to L11 down to a row, containing > a specific text, "Paid." is that possible Why not keep to the original thread and respond to Frank's suggestion? -- HTH RP "Jeff" <Jeff...

How do I make text italic in a publisher text box?
I've copied an item into publisher. The font automatically changed. I changed the font back to what is was originally. That worked, along with items that were once again bold. However, the entire text WAS italicized and now it's not. How do I italicize my text now? Select the text and italicize it. -- JoAnn Paules MVP Microsoft [Publisher] Tech Editor for "Microsoft Publisher 2007 For Dummies" "81gator" <81gator@discussions.microsoft.com> wrote in message news:35607A56-CAE8-4E6D-8FD3-FC6545B2AA9A@microsoft.com... > I've copied an item into pu...

How do I make a read only file?
In publisher - is there a way to make a read only file as you do for EXCEL or Word? Thanks Brenda You can change the attributes but if you send someone a .pdf file it will do the same thing. -- JoAnn Paules MVP Microsoft [Publisher] Tech Editor for "Microsoft Publisher 2007 For Dummies" "Brenda Mickelson" <brenda@dee-bee.com> wrote in message news:553CD213-E916-4E24-9F30-EAD0F1FEA628@microsoft.com... > In publisher - is there a way to make a read only file as you do for EXCEL > or Word? > > Thanks > Brenda ...

Changing Numbers to Text in a query
Hello, I have a number in a query that i need to change to text because my data, i.e. 555767 is being returned as 555,767 and i do not want it to contain a comma. Any ideas? Thank you! MN Perhaps you only need to change the "display" of the number, not the data type...? Regards Jeff Boyce Microsoft Office/Access MVP "MacNut2004" <MacNut2004@discussions.microsoft.com> wrote in message news:EF49F175-9640-4B44-961C-4A8EC6D5E4BB@microsoft.com... > Hello, > > I have a number in a query that i need to change to text because my data, > i.e. 555767 is b...

vlookup #44
I need a way of testing the return value from a vlookup call. It will happen that the lookup will fail and the return will be #N/A, which is not nicest thing to see on a spreadsheet! I have tried "if" statement variations but testing the cell is unsuccessful as the 'value'of the cell is not what is displayed! Jim Crawford Alden Press Oxford UK jcrawford@alden.co.uk To return an empty cell if a match is not found: =IF(ISNA(MATCH(D1,A1:A100,0)),"",VLOOKUP(D1,A1:C100,3,0)) -- HTH, RD ============================================== Please keep all corresponden...

How do I make flashcards from Excel spreadsheet?
My daughter's teacher has asked me to make index card sized flashcards for their classroom. I have put the words into an excel spreadsheet. Any ideas how to get each word out on it's own index card in large enough letters to read? You may want to use MSWord and excel and use mailmerge to create these flash cards. In fact, if you go to MSWord, you'll see that mailmerge supports lots of different things. Maybe you could go to your local office suppy store and get some card stock that MSWord supports. Then use that to print the flash cards. You may want to read some tips fo...