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

 

[Vanda_a]

How about posting a few rows worth of sample data and tell us what result you expect.

	A	B	C	D	E	F
1	1234567890123	A	890123	formula	Result from A column	From B
2	0123456789123	B	789123	formula	Result from A column	From B

<tbody>
</tbody>

Is it possible?
Many thanks

 

[T. Valko]

	A	B	C	D	E	F
1	1234567890123	A	890123	formula	Result from A column	From B
2	0123456789123	B	789123	formula	Result from A column	From B

<TBODY>
</TBODY>

Is it possible?





Many thanks

Like this...

Book1
	A	B	C	D	E	F
2	1234567890123	A	890123	formula	1234567890123	A
3	0123456789123	B	789123	formula	0123456789123	B
Sheet1

This array formula** entered in E2:

=INDEX(A$2:A$3,MATCH("*"&$C2,$A$2:$A$3&"",0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy across to F2 then down as needed.

 

[Vanda_a]

Like this...

Sheet1

	A	B	C	D	E	F
2	1234567890123	A	890123	formula	1234567890123	A
3	0123456789123	B	789123	formula	0123456789123	B

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:96px;"><col style="width:37px;"><col style="width:61px;"><col style="width:59px;"><col style="width:101px;"><col style="width:37px;"></colgroup><tbody>
</tbody>

This array formula** entered in E2:

=INDEX(A$2:A$3,MATCH("*"&$C2,$A$2:$A$3&"",0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy across to F2 then down as needed.

many thanks

 

[Vanda_a]

Like this...

Sheet1

	A	B	C	D	E	F
2	1234567890123	A	890123	formula	1234567890123	A
3	0123456789123	B	789123	formula	0123456789123	B

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:96px;"><col style="width:37px;"><col style="width:61px;"><col style="width:59px;"><col style="width:101px;"><col style="width:37px;"></colgroup><tbody>
</tbody>

This array formula** entered in E2:

=INDEX(A$2:A$3,MATCH("*"&$C2,$A$2:$A$3&"",0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy across to F2 then down as needed.

one more problem comes up. It says array formula are not valid in merged cells. please help

 

[Aladin Akyurek]

one more problem comes up. It says array formula are not valid in merged cells. please help

Try to unmerge the cells.

Does this work for cells merged and unmerged:

E2, just enter, copy across, and down:

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

 

[vinod9111]

amazing Aladin, how does the formula works?

 

[Aladin Akyurek]

amazing Aladin, how does the formula works?

Let BigNum = 9.99999999999999E+307

1. LOOKUP(BigNum,Reference)

yields the last numeric value from Reference, which can be an ordinary range (like A2:A4) or a calculated array (like {7,1,#VALUE!,2,#VALUE!}.

2. LOOKUP(X,Reference1,Reference2)

yields the correlate from Reference2 which is given by the position of X in Reference1.

3. LOOKUP(BigNum,Reference1,Reference2)

operates likewise and returns from Reference2 the corelate of the last numeric value of Reference1.

Example: LOOKUP(BigNum,{1,7,4},{"Kad","Nad","Jad"}) ==> Jad

4. The following are the kernel citations:

http://www.mrexcel.com/forum/excel-questions/102091-9-9999999-a.html (Post #3)
http://www.mrexcel.com/forum/excel-questions/310278-vlookup-multiple-matches-match-returned.html (Post #7, algorithmic description)

5. FIND(X,Reference)

does a search for X in Reference, yielding an array result, something like:

{8;#VALUE!}

Note that the last numeric value is 8 and this value is at position #1.

6. FIND(X&"|",Reference&"|")

adds to X a | at the end, making the search for a substring ending with |. The same action is taken with adding a | char to Reference at its end.

So, searching 123 in 2345123768 becomes searchin for 123| in 2345123768|, which will fail (as intended). But looking for 123 in 32489123 will succeed as intended. To re-cap: The action effects a filtering of "Ends With".


7. By [3] and [6], we can write:

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

which will determine the position of the value of C2 in $A$2:$A$3 and deliver the value in A$2:A$3 at that position.

Q.E.F.

 

[T. Valko]

one more problem comes up. It says array formula are not valid in merged cells. please help

What cell is merged?

 

[Vanda_a]

Try to unmerge the cells.

Does this work for cells merged and unmerged:

E2, just enter, copy across, and down:

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

It work with unmerged.

Great job.. Many thanks

 

[easy2understandexcel]

Let BigNum = 9.99999999999999E+307

1. LOOKUP(BigNum,Reference) ... ... ...


7. By [3] and [6], we can write:

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

which will determine the position of the value of C2 in $A$2:$A$3 and deliver the value in A$2:A$3 at that position.

Q.E.F.

My mind = blown.