VLOOKUP and RIGHT

**holiday4ever** · 07-04-2009, 01:10 PM

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.

**Ancalagon12321** · 07-04-2009, 01:18 PM

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.

**martindwilson** · 07-04-2009, 01:30 PM

Just add - -
=VLOOKUP(--RIGHT($A$3,LEN($A$3)-5),$A$9:$AD$20,3,false)

**holiday4ever** · 07-04-2009, 08:32 PM

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.

**martindwilson** · 07-04-2009, 09:07 PM

you dont need "value"

**Ancalagon12321** · 07-04-2009, 09:55 PM

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.

**martindwilson** · 07-04-2009, 10:06 PM

piffle and balderdash !

**holiday4ever** · 07-04-2009, 11:10 PM

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