Vlookup only the last 6 characters

I have a spreadsheet starting in cell A4. I want to lookup only the last 6 
digits in column A and then lookup the value in column J and return the 
appropriate value from column K.

The values in column A are 000322341; 000333456; etc.

The values in column J are 322341; 333456; etc.

Here is my current non working formula...
=VLOOKUP(RIGHT(A4,6),$J$4:$K$7,2,0)

Any help would be appreciated. Thanks.
0
Utf
11/14/2009 8:49:02 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
4328 Views

Similar Articles

[PageSpeed] 28

The RIGHT function is returning a text value, and I'm guessing that the 
values in column J are true numbers.  So you'd need to covert the text back 
to numbers in order to have the lookup work properly:
=VLOOKUP(VALUE(RIGHT(A4,6)),$J$4:$K$7,2,0)

"JoeP" wrote:

> I have a spreadsheet starting in cell A4. I want to lookup only the last 6 
> digits in column A and then lookup the value in column J and return the 
> appropriate value from column K.
> 
> The values in column A are 000322341; 000333456; etc.
> 
> The values in column J are 322341; 333456; etc.
> 
> Here is my current non working formula...
> =VLOOKUP(RIGHT(A4,6),$J$4:$K$7,2,0)
> 
> Any help would be appreciated. Thanks.
0
Utf
11/14/2009 9:30:01 PM
Fantastic - thank you so much.

"bapeltzer" wrote:

> The RIGHT function is returning a text value, and I'm guessing that the 
> values in column J are true numbers.  So you'd need to covert the text back 
> to numbers in order to have the lookup work properly:
> =VLOOKUP(VALUE(RIGHT(A4,6)),$J$4:$K$7,2,0)
> 
> "JoeP" wrote:
> 
> > I have a spreadsheet starting in cell A4. I want to lookup only the last 6 
> > digits in column A and then lookup the value in column J and return the 
> > appropriate value from column K.
> > 
> > The values in column A are 000322341; 000333456; etc.
> > 
> > The values in column J are 322341; 333456; etc.
> > 
> > Here is my current non working formula...
> > =VLOOKUP(RIGHT(A4,6),$J$4:$K$7,2,0)
> > 
> > Any help would be appreciated. Thanks.
-1
Utf
11/15/2009 12:47:02 AM
Reply:

Similar Artilces:

Removing IMS from last 5.5 server
I've got all mail going in and out from my 2003 server, the IMS on the 5.5 box has been stopped. So I decided I'd just follow through with the last step from 883407 (step 10) Where it says we can just delete the IMS. Well when I expand my 'connections' in the 5.5 administrator and try to delete the Internet Mail Server (Machinename) I get an error message that reads: "cannot find the file c:\program files\exchsrvr\smtp.uns error: C1037986" Is this really even a big deal if it's no longer doing any routing and I plan to have the box down within a few da...

If statement #6
Is there a way to have an if forumla populate a cell if another has data in it? i.e. =if(c3=x, a2+1,"blank") so if c3 = any data text or otherwise a3 would equal a2+1 and if there was nothing in c3 then the cell is left blank. Is there a way to do this? A. =IF(C3=YourCriteria, A2+1,"") -- Greeting from the Gulf Coast! http://myweb.cableone.net/twodays "Andr�" <anonymous@discussions.microsoft.com> wrote in message news:083001c3bd9f$90980f00$a401280a@phx.gbl... > Is there a way to have an if forumla populate a cell if > another has data in it? &...

possible to equisize charts (6) in chart sheet?
My chart sheet has six charts on it; arranged in two columns of three charts each. The "snap to grid" feature is nice yet I am having difficulty getting all six of the charts to be the same size/dimension. Is there a shortcut for doing this or is it necessary to simply "eyeball" the heights and widths of the charts? I am hoping there is a way to automatically "tile" the charts. Thank you. Andrew - I usually use a regular worksheet for this, a blank one without all those numbers cluttering it up. When you drag and stretch charts on a worksheet, holding...

really neat features Quicken incorporated last year ...?
There's a quote in the unofficial FAQ for this newsgroup (http://www.bollar.org/msmoney) that asks 'When will the next version of Money release? What will it do?'. In the answer is this statement: "It will incorporate any really neat features Quicken incorporated last year if those features generated much "buzz" amongst reviewers or by the Intuit marketeers." So, not being familar with Quicken, (other than looking at the boxes at Costco and Sam's Club), what really neat features has Quicken incorporated? The reviews I read this year didn't ment...

Need to append a character to cell data
Hello everyone; I need to append a character to the beginning of a string in Excel. The problem is, this needs to happen automatically, so the page acts like a template. Basically, I would like to put an apostrophe at the beginning of a string in a range of cells, so when a user copies a list and pastes it into a column, each entry has an apostrophe added to the beginning. This would make the following list: 000101 000102 000103 look like this in the cell: '000101 '000102 '000103 Any information/assistance would be appreciated. If possible, I would like to accomplish this ...

Opening an Excel File #6
I have Office 2002 and can not get the Excel files to open up without opening Excel first. I should be able to double click the file and it opens up Excel and the file. It does that in all the other office applications but this one. It used to do that and now it has been changed or I changed something and don't know what it is to get it back. Can someone help me? Following should be all you need to fix the problem; unsure as to why it ocurred in the first place though: 1) Open a folder on your PC. Almost any will do. 2) Select 'Folder Options' from the 'Tools' m...

Vlookup,format general, format text
Hi, I am having a pull the hair out problem with excel. I have a workbook that I use vlookup to get data from a datasheet in the same workbook. The data is a combination of text and numbers and text and numbers combined. Excel sometimes returns the values I want and sometimes doesn't. Sometimes I have to format the data as text and vlookup will work and sometimes not. Then I switch the data to general and it will work and sometimes not. This is extremely frustrating. Now I have a few cells that will not return anything but N/A no matter what format I have it in. I know that that dat...

smartlist #6
Is there anyway to distinguish the method of payment for a sale in sales line items. We use ver 8 and I want to pull a list of sales, but only for a/r customer not for cash or credit card payments. -- Rick You could use Payment Received = 0, perhaps, to show invoices where there is no payment. -- Charles Allen, MVP "Rick at ACFB" wrote: > Is there anyway to distinguish the method of payment for a sale in sales line > items. We use ver 8 and I want to pull a list of sales, but only for a/r > customer not for cash or credit card payments. > -- > Rick ...

Great Plains 7.5 and Acrobat 6 Std...more woes
I'm running Great Plains 7.5 SP6 in a Terminal Services environment. Everything works great using Abrobat 5. Once I try to upgrade to Acrobat 6, it goes completely haywire. On my standalone PCs that run GP 7.5 and Acrobat 6, it runs perfectly and there are no problems whatsoever. The Terminal Server is another story though. I know that Acrobat, when it creates the PDF in the Send To (PDF) function, it saves it in the Great Plains install directory. I've given my users full access to just that directory. I've given them full access to the Adobe directory and also full ...

vlookup help #2
hi i am providing you with my problem with attached file. i will be very grateful to you if you can solve my probem Attachment filename: help me.xls Download attachment: http://www.excelforum.com/attachment.php?postid=46482 -- Message posted from http://www.ExcelForum.com Hi first: you should try to explain your problem in plain text. Most people won't open attachments :-) To your problems: Just enter the 4th parameter of the VLOOKUP function to look for exact matches. So change =VLOOKUP(A17,A25:C27,3) to =VLOOKUP(A17,A25:C27,3,0) and =VLOOKUP(A17,A25:C2...

Returning last number(time) in a row of cells
Need some help with this one please; Row A4 thru J4 A4 B4 C4 D4 E4 F4 G4 H4 I4 J4 12:00 15:00 16:00 I need to return to cell K4 the last number in the above row. I do not know what formula to use. Any help would be greatly appreciated. There are several ways to achieve that, - here are two: http://img9.imageshack.us/img9/9398/nonamegi.png Micky "v1rt8" wrote: > Need some help with this one please; > Row A4 thru J4 > A4 B4 C4 D4 E4 F4 G4 H4 I4 J4 > 12:00 15:00 16:00 > ...

Outlook Express 6 Language Option
I am running XP SP3 and have just installed Office 2007 Home and Student edition. Since doing this the spell checker in Outlook Express has started to spellcheck in FRENCH. Tools/Options/Spelling shows the Language as French but the drop down meue has no alternatives. Any ideas how I get it back to English? This newsgroup is for support of Outlook 97, 98, 2000, 2002, 2003 & 2007 from the Office family for PCs. For Outlook Express [OE] support try posting in one of these newsgroups: microsoft.public.inetexplorer.ie4.outlookexpress for OE 4.x microsoft.public.windows.inetexp...

Return cell characters after space
I have a cell with 'Mr J. Smith' and want to return in another cell 'Smith'. As there are many other examples, I need a function that will seek out the last ' ' in the cell content and then return all characters to the right hand-side of this ' '. e.g. 'Mr K Peters' would return 'Peters' Please let me know if I can clarify! Hi Andy =TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",255)),255)) -- Jacob (MVP - Excel) "Andy" wrote: > I have a cell with 'Mr J. Smith' and want to return in an...

Need to obtain sum of last X entries that are greater than 0
Extreme thanks in advance... A1=20 A2=40 A3=10 A4=50 A5=0 A6=80 A7=0 A8=0 In the above example column, assuming that the last four >0 are needed, the result would be A2+A3+A4+A6. If A7 is updated to something >0, the result would be A3+A4+A6+A7. Hi! What if there aren't 4 values to sum? Entered as an array using the key combo of CTRL,SHIFT,ENTER: =SUM(A100:INDEX(A1:A100,LARGE(IF(A1:A100>0,ROW(A1:A100)),4))) Use a big enough range to allow for added data. You can't use the entire column, though. Biff "Blue 58" <Blue58@set.hut> wrote in message news...

COM specs for VS 6
About three years ago i've downloaded a complete specifications of VS6 COM interface from somewhere. IIRC that were 200+ pages or so. That was in the other company. And now I need it back but can't find it anymore. If someone could tell where to find, you'd make me really happy. TIA, Hans -- The one who doesn't know the answer is wiser than the one who knows the wrong answer. ...

Change of dir of project = Error in MVC++ 6
Hi, guys; I know that what I'm gonna tell sounds almost imposible, but thats why I'm so surprised and don't know what to do: I'm making some changes in a program with VC 6. Every day, when I finish my work, I make a copy of the dir where the code is. Well, now, when I try to run the workspace, and de MVC is loaded it gives an error. I went back to my old copies, and got one with no error. What really surprises me is that, if I change the name of the folder where the code is, MVC gives an error. And if I leave the same name of the dir, it doesn't. That makes no sense fo...

Problem with displaying characters in excel sheet
I an excel sheet, I am displaying a character alpha "?". But when I retrieve this chaarcter though a messagebox function in VB editor, say msgbox mysheet.cells(4,5) then it shows "a" and not alpha... What am I missing ? TIA. How a character displays is depenent on the font used to display it. Perhaps you want to use a Userform/textbox or label to display it since that would allow you to specify a font to display it with. -- Regards, Tom Ogilvy "Noee" <anonymous@discussions.com> wrote in message news:uipdIwRsEHA.2688@TK2MSFTNGP14.phx.gbl... > I an...

How do I get a popup window on a VLOOKUP lookup failure?
I am using the VLOOKUP function and want to get an error indication if there is not an exact match for the first argument (lookup_value). I can get an error indication each cell with a problem by using the 4th argument to VLOOKUP and using the value "FALSE". How do I get a indication that there a problem in either a fixed location in the spreadsheet or a popop window? I am using Office 2003, but, if need be, I can move to Office 2010. I am using Windows XP and cannot upgrade. Assumes your lookup formula is in cell H4... Enter this formula in your "fixed location" cell:...

Terminal on Win CE 6.0 R2
We use MIPSII platform under Win CE 6.0, Terminal application is unable to talk to a PC with our board's COM ports. We use our own written CE 6.0 application to do communication with PC's hyperterminal using those COM ports from our platform and it works. The same platform under Win CE 5.0 can use Terminal application and can talk to a PC hyperterminal and is working correctly We've done line by line source comparison and did not find any differences in code for COM ports at PDD/MDD level. Any suggestions ? Thanks, GS-ICN do you mean the standard Wind...

LDAP #6
When ever i start up outlook 2002 - a window pops up labled Microsoft LDAP Directories. With in the window is a tab labled Connection Details. Under the tab server name : NULL port: 3268 username: NULL password: - The server name and port number are unchangeable! What is this window and what does it apply too? - This window also appears when i'm using Intellisync for MSN!! You should remove the LDAP (might read Active Directory) address book. See Tools | Email Accounts | View/Change address books (3rd or 4th radio button). -- Neo [MVP Outlook] Due to the Swen virus, all e...

... Count Function ... with any character ...
I have a count function; ( =COUNT(C4:Z4) ) I have my cells formated to display an (" X ") for any number that is entered. The function works well. But, I'd like to know if there is a way I can type any character into the cells instead of just numbers? (I'd like to have the "any character" be displayed as (" X ")). Darrell Dr. Darrell, If you want to count the cells that contain any character (numbers as well as strings), use =COUNTA(C4:Z4) Regards, B.R.Ramachandran "Dr. Darrell" wrote: > I have a count function; ( =COUNT(C4:Z4) )...

Vlookup and IF function problem
Hi, I would like to do a kind of double Vlookup. I have 2 different worksheets with following information: Worksheet1 is the master file with all modells: 3 columns A B C B segment Honda Civic B J segment Honda Civic Coup� D segment Toyota Avensis D ... Worksheet2 is the data file: 3 columns, of which 2 are filled A B C B segment Honda Civic ... J s...

How is tabbed view accomplished in VC++ 6?
Hi folks, Looking at the resource editor in VC++ 6, it is not very clear how to add multiple sets of resources, one per tabbed view. Is this something that is not possible in version 6 of VC++? Thanks. Snuffelluffogus wrote: > Hi folks, > > Looking at the resource editor in VC++ 6, > it is not very clear how to add multiple sets > of resources, one per tabbed view. > > Is this something that is not possible in version 6 > of VC++? > > Thanks. On the 'Insert' menu select 'Resource'. Is this what you are looking for? -- Scott McPhillips...

Using the wildcard * character
=SUMPRODUCT((Description!$B$1:$B$5000=A2) (Description!$A$1:$A$5000=$B$1) * (Description!$C$1:$C$5000="2.0")) Hi I'm using SUMPRODUCT to find matching data within 3 columns. At th end of the statement above I have ...="2.0")), but what I want it to d is to match the cells that start with 2.0 and then have anything els after it. I know I have to use the * character but I am not sure how. Thanks for your help! R -- Message posted from http://www.ExcelForum.com Hi, Try changing the last part to something like this: Match those starting with 2.0 ....*(LEFT(Descri...

First Name Last Name
I have a form to enter a First Name Last Name from a keypad on screen. It works fine to input both First and last name into one field; however is there a way that when I save the record I can split the first name and the last name into seperate fields? I know I can enter them seperatly but I'm trying to do it in one shot. Is there a function that finds a space between two words, then perhaps another function to enter whats left of the space, then another to enter whats on the right? Any help is appreciated Thanks DS On Sat, 21 Jul 2007 16:29:52 -0500, DS <bootybox@optonline.net...