Forum Statistics
- Forum Members:
- Total Threads:
- Total Posts: 9
There are 1 users currently browsing forums.
|
 |
07-04-2009, 12:10 PM
|
|
Registered User
|
|
Join Date: 06 May 2008
Posts: 16
|
|
|
VLOOKUP and RIGHT
Please Register to Remove these Ads
I seem cannot get the code right, see below.
Cell: A3= Week 5
=VLOOKUP(RIGHT($A$3,LEN($A$3)-5),Lane!$A$9:$AD$20,3)
It won't work....
However, if I tried as follows,
=VLOOKUP(5,Lane!$A$9:$AD$20,3) OR
=RIGHT($A$3,LEN($A$3)-5)
Both works fine!!!
But I do rather something like as it is shown on the above in RED TEXT
Also I am worried if I have Week 10 the output will look as 0 instead of 10 so please advise what is the right code.
Thank you for your help.
Last edited by holiday4ever; 07-05-2009 at 03:45 AM.
|
07-04-2009, 12:18 PM
|
|
Registered User
|
|
Join Date: 03 Jul 2009
Location: London, England
MS Office Version:Excel 2003
Posts: 17
|
|
|
Re: VLOOKUP and RIGHT
The problem here is the distinction between numbers and text strings.
The RIGHT function is returning the text string "5". But your column of numbers doesn't contain "5" it only contains the number 5.
To convert a text string into a number use VALUE. E.g. VALUE("5") = 5.
|
07-04-2009, 12:30 PM
|
 |
Forum Guru
|
|
Join Date: 23 Jun 2007
Location: London,England
MS Office Version:office 97 ,2003 ,2007
Posts: 6,084
|
|
|
Re: VLOOKUP and RIGHT
Just add - -
=VLOOKUP(--RIGHT($A$3,LEN($A$3)-5),$A$9:$AD$20,3,false)
|
07-04-2009, 07:32 PM
|
|
Registered User
|
|
Join Date: 06 May 2008
Posts: 16
|
|
|
Re: VLOOKUP and RIGHT
Thanks Ancalagon12321 and martindwilson. I add "VALUE" see following details....
=VLOOKUP(VALUE(RIGHT($A$3,LEN($A$3)-5)),Lane!$A$9:$AD$20,3)
=VLOOKUP(VALUE(--RIGHT($A$3,LEN($A$3)-5)),Lane!$A$9:$AD$20,3,FALSE)
Both works fine. Thank you very much.
Last edited by holiday4ever; 07-04-2009 at 07:50 PM.
Reason: To correct my answer
|
07-04-2009, 08:07 PM
|
|
Forum Guru
|
|
Join Date: 23 Jun 2007
Location: London,England
MS Office Version:office 97 ,2003 ,2007
Posts: 6,084
|
|
|
Re: VLOOKUP and RIGHT
you dont need "value"
|
07-04-2009, 08:55 PM
|
|
Registered User
|
|
Join Date: 03 Jul 2009
Location: London, England
MS Office Version:Excel 2003
Posts: 17
|
|
|
Re: VLOOKUP and RIGHT
martin:
True, but you don't need "--" either 
I prefer using VALUE because double-minus only "just so happens" to work, thanks to Excel's implicit type-casting rules, whereas the VALUE function was put there for express purpose of turning text into numbers.
So I think using VALUE is clearer for any non-Excel-guru who has to understand and maintain the formulas.
|
07-04-2009, 09:06 PM
|
|
Forum Guru
|
|
Join Date: 23 Jun 2007
Location: London,England
MS Office Version:office 97 ,2003 ,2007
Posts: 6,084
|
|
|
Re: VLOOKUP and RIGHT
piffle and balderdash !
|
07-04-2009, 10:10 PM
|
|
Registered User
|
|
Join Date: 06 May 2008
Posts: 16
|
|
|
Re: VLOOKUP and RIGHT
Thanks again to you both for pointing this out, martindwilson yeah you are right I do can do without the value. So I understand that I can use either of them. I am looking to improve my knowledge especially in Excel formulas and functions so I am wondering could anyone send a private email of recommended books to me. Thanks
|
07-04-2009, 11:08 PM
|
|
Forum Contributor
|
|
Join Date: 11 Nov 2008
Location: The Earth
Posts: 194
|
|
|
Re: VLOOKUP and RIGHT
holiday4ever
If that takes care of your need, Plz, EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].
|
|
|
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
|
|
|
| Thread Tools |
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
