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
4400 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:

Physical inventory #6
I used 3 systems to take inventory, at the end of the day I received an error message stating that "File could not be saved , because it was changed by another user" We were all working on the same physical inventory file. Is my error in that I should have 3 files open and each system work on its own file? Will the inventory numbers come out correctly, if we use 3 files? Any help would be greatly appreciated. akpetshop wrote: > Is my error in that I should have 3 files open and each system work on its own > file? Yes.. is your error. You can't use the same file from...

Vlookup #41
Looking for someone who knows vlookup. http://www.contextures.on.ca/xlFunctions02.html Gord Dibben MS Excel MVP On Thu, 19 Jul 2007 16:22:00 -0700, trisher75 <trisher75@discussions.microsoft.com> wrote: >Looking for someone who knows vlookup. Can you be a little more specific on what you want to do with the vlookup? "trisher75" <trisher75@discussions.microsoft.com> wrote in message news:AF750FF1-876C-471F-8EBB-CE747EAE67B9@microsoft.com... > Looking for someone who knows vlookup. Have you tried the help index for VLOOKUP? -- Don Guillett Microsoft MV...

Removing last character of cell
I am trying to remove a comma which happens to be the last character in the cell. Does anyone know how to do this? Mira, You can use a combination of the Left and Right functions. Insert a blank column to the right of column you wish to change. In my example I will assume the data is in column A. Change the formula as necessary. In the empty cell enter the formula: =IF(RIGHT(A1,1)=",",LEFT(A1,LEN(A1)-1),A1) This formula is checking to see if the last character in cell A1 is infact a comma. If it is the formula will return all of the characters in cell A1 except the last...

CRM 1.2 - editing the IIS 6.0 metabase
I have CRM 1.2 installed into the default website of my SQL server. IIS 6.0 assigns the metabase identifier of "1" to the default website. I would like to create a new website in IIS and assign it the identifier of "1" so that I can install SRS into the new website. This would require me to edit the identifier of the CRM website with the Metabase Explorer included in the IIS 6.0 Resource Kit. Can I change this identifier on the CRM website, or will that screw something up? -- thanks, -Jeff If anyone cares, I decided to edit the metabase, install SRS, then edit ...

Problem Displaying Non-English (UTF-8) Characters
Hi. I`m having a problem with Excel displaying non-English (UTF-8 format) characters in a column. Instead of displaying the characters, it displays "?" for each character. I have selected the appropriate non-English language under Editing Language settings. This occurs on both Excel 2003 and 2007. Is there a way to get these characters to display correctly? Thanks, Alan ...

one last error, exchange 2007
once ive cleared this one the event viewer will be devoid of red exes! Event Type: Error Event Source: MSExchangeFBPublish Event Category: General Event ID: 8207 Date: 28/02/2007 Time: 12:48:50 User: N/A Computer: XXXXXX Description: Error updating public folder with free/busy information on virtual machine XXXXXXX. The error number is 0x80004005. For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp. any solutions for a fix on 2007 ? On Wed, 28 Feb 2007 13:35:35 -0000, "luna" <luna_s@themoon.com> wrote: >once ive cleare...

Display last "Heading 1" in footer
Hi, I have 45 page document with a contents page using Heading 1 and Heading 2. The Heading 1 are my major titles and I would like them to also display in the footer of every page. Is there a way to do this? For example, the first Heading 1 is Contents so all pages (including the one with the Contents title in it) should have Contents in the footer. The next one is Communications & Performing Arts, so that page and all following pages should stop displaying Contents and should start displaying Communications & Performing Arts instead until we get to the next Heading 1 and...

VLOOKUP function part 2...
Apologies for the repeat thread, just there is so much traffic here that the last part of my query went without an answer. Sheet "Employee Data" holds usernames of about 1500 employees in the format "joe.bloggs" in column A and employee payroll numbers in the format 00001, 00002, etc in column B I am looking for a formula so that in another worksheet, where an employee types a name in the format "Joe", "Bloggs", "joe.bloggs" or Joe Bloggs" in the cell in column C, it will return the relevant payroll number in column D. If t...

Upgraded to Excel XP, Excel 97 Vlookup's corrupted
We would like to upgrade to Excel 2002 or(XP) however we have a huge amount of excel 97 files that are loaded with Vlookup formulas similar to =VLOOKUP('F:\Prices-Dec 2004-Master Program\Master Pricing - Pacesetter.xls'!Joist_Package_2,'F:\Prices-Dec 2004-Master Program\Master Pricing - Pacesetter.xls'!Prices_To_Change,MATCH('F:\Prices-Dec 2004-Master Program\Master Pricing - Pacesetter.xls'!Apaloosa_A_IX_Series,'F:\Prices-Dec 2004-Master Program\Master Pricing - Pacesetter.xls'!Model_List,0)+2,FALSE) When we load on Excel 2002 all our Vlookup results ha...

Password Protection #6
Is there any way to manipulate a password protected sheet without having the actual password? J.E. McGimpsey has a way! http://www.mcgimpsey.com/excel/removepwords.html stew wrote: > > Is there any way to manipulate a password protected sheet > without having the actual password? -- Dave Peterson ec35720@msn.com Password remover: http://www.straxx.com/excel/password.html "stew" <day2399@aol.com> wrote in message news:11a3a01c441ce$b63b3b20$a501280a@phx.gbl... > Is there any way to manipulate a password protected sheet > without having the actual pa...

VC++6 OLE export to MS Project
I didn't know which group to use for this question, so I have chosen 3. Hope I haven't annoyed anyone!... I'm using VC++ 6.0. I am writing an export facility to MS Project. I have exported a bunch of tasks to MS Project. I have exported resource information and successfully attached resources to the tasks. Question 1: I have had to do this all by guess work as I can't find any help system anywhere that says which methods do what and what parameter types to use. Is there a help system detailing what methods are available to C++ through OLE, as I am sick of having to randomly ...

Special characters for foreign text
I have several contacts in Italy or France. Can anyone help me figure ou how I can add accents and other special characters into Microsoft Office products? Marina Post <marina@rockethockey.com> wrote: Hi Marina, > I have several contacts in Italy or France. Can anyone help me figure > ou how I can add accents and other special characters into Microsoft > Office products? Same way as you'd do in other apps. You can use shortcuts on the keyboard. the Keycaps application, which is part of the system, should help you figure out what you need to type (it depends...

Excel #6
Quando executo uma planilha direto pelo nome, demora de abrir, por que? E quando executo o excel primeiro e busco o arquivo abre com rapides. ...

Strange characters in signature
Outlook 2000 is putting odd characters in my new messges,=20 but only when I have a signature defined. This happened=20 recently, after years of no problems! My signature now=20 looks like this: =20 =EF=BB=BF=20 David M. Foster Without a signature, a new message looks like this: <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0=20 Transitional//EN"> <HTML><HEAD> <META http-equiv=3DContent-Type content=3D"text/html;=20 charset=3Dutf-8"> <META content=3D"MSHTML 6.00.2800.1276"=20 name=3DGENERATOR></HEAD> <BODY> <DIV>&am...

limitation of characters in query?
I am trying to write a condition in my query using IIf statement and the statement is quite long. But I facing a problems that I am not able to type out all the condition in the query. Is there any limits for character type in the query field? On 9 Apr 2007 19:10:02 -0700, xiaodan86@hotmail.com wrote: >I am trying to write a condition in my query using IIf statement and >the statement is quite long. >But I facing a problems that I am not able to type out all the >condition in the query. >Is there any limits for character type in the query field? Yes: 1024 bytes if I recall a...

outlook express 6.0 #9
Help me please. I have recently discovered OE 6.0 has removed access to the following unsafe attachments in your mail, being sent to me at home. I need these attachments for some school work. How do I change this? Thank-you! Terry Post your question in the Outlook Express newsgroup. This is for Office Outlook: http://www.microsoft.com/windows/ie/dgbrowser/en-us/default.mspx >-----Original Message----- >Help me please. I have recently discovered OE 6.0 has >removed access to the following unsafe attachments in your >mail, being sent to me at home. I need these attachments &...

Personal folders #6
I was moving email from one personal folder to another and in the middle the move failed. when i went back to see what moved some of the email moved over but just the title did and in the old personal folder the email was deleted. is there a way to undelete the email from the orignal personal folder ? thanks Erich Kaiser <erich.kaiser@eds.com> wrote: > I was moving email from one personal folder to another and > in the middle the move failed. > > when i went back to see what moved some of the email moved > over but just the title did and in the old personal folder...

VLookup and Indirect
I am currently using INDIRECT to get the lookup_value for VLOOKUP The formula I am using is: =VLOOKUP(INDIRECT(D2),INDIRECT("'"&B2&"'!A2:F20"),2,) where D2 is a text string (example:Akan 1-1) When I use the above formula I get #Ref If I place D2 in quotes: =VLOOKUP(INDIRECT("D2"),INDIRECT("'"&B2&"'!A2:F20"),2,). The correct result is returned. However this locks the reference to D2. I need to be able to use this code so that If I move it to the 3rd column it will read D3 and I would prefer n...

Printing #6
Hello! I have an address data base and want to print it into a catalog form. The way that each column (fields:name,address, etc) will print after each other. Is the database currently in one column? Or across rows? Is the data consistent as far as entries? i.e. name address city state zip in one column and you want name address city state zip in 5 columns? Copy/paste a sample of current data layout and what you want print layout to be. Gord Dibben MS Excel MVP On Thu, 3 Jul 2008 14:04:01 -0700, Cesia <Cesia@discussions.microsoft.com> wrote: >Hello! >I have ...

sORTING #6
Hi i want to auto sort a league range C4:G19, i have tried the auto sort routine:- Private Sub Worksheet_Change(ByVal Target As Range If Not Intersect(Target, Range("C4:G19")) Is Nothing The Range("C4:G19").Sort Key1:=Columns("G" End I End Su Which works ok if i input the figures into the league, but the league is updated from a seperate sheet using the IF command, but the league doesnt auto sort from a seperate shee Any clues please I think it would kind of drive me nuts to have it sort automatically (I run your macro on demand.) But try putting your sort st...

Count last nonzero number in a column
Hi I have columns of numbers, A:Z. Each column has either numbers, zero's, or text. I need to find the row number that has the "Last" nonzero number. So if A1="car" , A2=0, A3=6, A4="Boat" I would need a value of 3, the last occurence of a nonzero number (6). Thanks for your help Hi try the following formula: =LOOKUP(2,1/((A1:A100<>0)*(ISNUMBER(A1:A100))),ROW(A1:A100)) -- Regards Frank Kabel Frankfurt, Germany "Jeff" <Jeff@discussions.microsoft.com> schrieb im Newsbeitrag news:FEA7DC58-6459-4F55-9DE4-984740EFC610@microsoft.com.....

Time Format #6
I'm having a problem adding together journey times. I have a start time (say 8:34:00) and an end time (say 10:23:00) and from this I calculate a journey time (1:49:00). I then add this journey time (using the sum function) across a number of different journeys giving me a total. This total is in some case in excess of 24 hours and Excel seems to revert to 0 at 24 hours and start the sum again. So for example I can see when I highlight the range that the total is 27:49:00 but the sum function returns 03:49:00. Any ideas? Cheers Lee Use a custom format: [hh]:mm:ss The []...

AmEx problems over the last several days
I've seen this posted in the context of upgrades from M04 / M05 -> M06. I'm running M05 Dlx-no upgrade yet, but I've had my several AmEx accounts under a single ID (straight connection to AmEx, no Passport/Yodlee) fail with "AmEx can't process your request right now. Try again later" since about 7/30 or so. Seemed strange, so I disabled Online Services for American Express and reenabled them, and it cleared up the problem. I don't know if something changed on the AmEx side, but I hope this helps someone... - John amex is currently changing their ...

Finger Friendly Theme for Wince 6.0
Hi, I m developing a Wince 6.0 OS for a KIOSK product. Practically we have to support "FINGER" based touch. My questions are, 1. Does Wince 6.0 Provides a Finger-Friendly theme (With large icons, buttons....) 2. Does it has a "Finger" friendly touch algorithm for a 5-wire touch pad? Indika No, I'd say that it doesn't. Since CE itself will run on devices with displays of pretty much any size, I don't think that there's really any way to say that icons of size X are big enough, too big, or too small. On a 1" VGA display, even a 64x...

Cell Formating #6
How to make a cell twinks when a condition is met in excel ? Hecwill, You've probably seen blinking formatting in Word. Excel has no such formatting. It's possible, though not recommended to use timed macros to do this. It works by changing the formatting periodically. -- Earl Kiosterud www.smokeylake.com "Hecwill" <Hecwill@discussions.microsoft.com> wrote in message news:56628B2B-0AA6-4924-8CD6-C10742C29CEF@microsoft.com... > How to make a cell twinks when a condition is met in excel ? Why is it not recommended? "Earl Kiosterud" <someone@n...