A simple vlookup question

[eggman2001]

Hello,

So I have the text "abc301" in cell A1 and "301" in cell A2. When I do
a vlookup using A2 as the lookup value, it works. However, if I use
RIGHT(A1, 3) as the lookup value, I get a N/A. Does anyone know how I
can use the 3 final characters in a cell as the lookup value?

 

[JP Ronse]

Hi,

I do not really understand what you want to do. Is cell A2 (301) a figure or
text?

Try this.

=VLOOKUP(VALUE(RIGHT(A1;3));A1:A2;1;FALSE)

Wkr,

JP

 

[Shig]

That works. Guess I needed the VALUE() function in there.

 

[Shig]

That seems to work for numbers, but not for text...

So lets say I have the text "abc301" in A1 and text "abcdef" in A2. I
want to use the last three characters of A1 as a lookup value and also
the last three characters of A2 as a lookup value.

 

[JP Ronse]

Hi Shig,

Your remark is correct but the question is rather difficult to answer as I
do not have any idea how you did set-up your spreadsheet.
I can have a look into if you send some more data for the lookup range and
the values that you want to look up.

Wkr,

JP

 

[Shig]

Hi JP,

I've uploaded an excel workbook here:
http://www.mediafire.com/download.php?it1lkyt1mwl

It should be fairly straightforward if you look at it.

 

[JP Ronse]

Hi Shig,

Try this ...

=IF(ISERROR(VALUE(RIGHT(A7;3)));VLOOKUP(RIGHT(A7;3);$H$2:$I$3;2;FALSE);VLOOKUP(VALUE(RIGHT(A7;3));$H$2:$I$3;2;FALSE))

Depending on your settings, you have to replace the ";" by ",".

The ISERROR returns true if the string cannot be converted to a number.

This will not work if the lookup table contains numbers formatted as string.

Wkr,

JP

 

[Max]

One other thought is to convert the mixed data match array (ie H2:H3)
to text using: &"" for consistent matching

In C2, normal ENTER:
=INDEX(I$2:I$3,MATCH(TRUE,INDEX(H$2:H$3&""=RIGHT(A2,3),),0))
Copy down

And if you need an error trap, use ISNA on the MATCH part of it,
indicatively like this:
=IF(ISNA(MATCH(...)),"",INDEX(...))

With the error trap, in C2, copied down:
=IF(ISNA(MATCH(TRUE,INDEX(H$2:H$3&""=RIGHT(A2,3),),0)),"",INDEX(I$2:I$3,MATCH(TRUE,INDEX(H$2:H$3&""=RIGHT(A2,3),),0)))

Success? Hit the stars below in google
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik