vlookup laste 6 digit.

[Vanda_a]

hello all. please help me out with this.

in sheet1 I have a data with 13digit barcode.
and in sheet2. I have only last 6digit of the barcode.
Can I use vlookup for this thing? vlookup 6digit to the 13digit barcode.

Thanks for help

 

[Aladin Akyurek]

My mind = blown.

 

[Aladin Akyurek]

It work with unmerged.

Great job.. Many thanks

You are welcome. Thanks for providing feedback.

 

[T. Valko]

Here's some additional information you may find useful.

=LOOKUP(9.99999999999999E+307,FIND($C1&"|",$A$2:$A$3&"|"),A$2:A$3)

You're searching strings that are 13 characters long and you want to look at the last 6 characters in those strings.

If the FIND function finds the last 6 characters it will return the number 8 (the starting character postion of the 6 character look up value).

For example:

1234567890123

The lookup value is 890123. The FIND function will return 8 because the lookup value 890123 is found starting at character number 8 within the string 1234567890123.

So, in this application the FIND function can only return 2 variables: the #VALUE! error when the lookup value is not found or the number 8 when the look value is found.

In this application, the lookup value:

=LOOKUP(lookup_value,...)

Simply needs to be larger than any number returned by the FIND function. The largest number that the FIND function can return is 8 so all we need for a lookup value is a number greater than 8.

Therefore, we can save a few keystrokes by using a smaller lookup value:

=LOOKUP(9,...)

 

[Vanda_a]

What cell is merged?

I mean merged cells. but I tried unmerged them already.

thanks very much