vlookup laste 6 digit.

Vanda_a

Well-known Member
Joined
Oct 29, 2012
Messages
934
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
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
If you trying to compare One column...then you can use..

=Vlookup(lookupvalue,right(tablearray,6),1,0)


Eg:
=VLOOKUP(C4,RIGHT($A$1:$A$17,6)+0,1,0)



Don't 4et to hit CTRL+shift+Enter

Hope this will help you..!
 
Upvote 0
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
Maybe this...

Book1
ABCDE
21234567111111A_333333C
34789561222222B___
43698751333333C___
59987589444444D___
Sheet1

This array formula** entered in E2:

=INDEX(B2:B5,MATCH("*"&D2,A2:A5&"",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.
 
Upvote 0
If you trying to compare One column...then you can use..

=Vlookup(lookupvalue,right(tablearray,6),1,0)


Eg:
=VLOOKUP(C4,RIGHT($A$1:$A$17,6)+0,1,0)



Don't 4et to hit CTRL+shift+Enter

Hope this will help you..!

I have just tried it but it not work. more detail please. about column and row.

13digit barcode at column A?
vlookup C4? so I copy and paste your formula at C5?
how to change to like this? sheet 1, column A is 13dgit barcode. and sheet2, column A is 6 digit barcode. then key in your formula at B1.

many thanks
 
Upvote 0
Maybe this...

Sheet1

ABCDE
21234567111111A_333333C
34789561222222B___
43698751333333C___
59987589444444D___

<tbody>
</tbody>

This array formula** entered in E2:

=INDEX(B2:B5,MATCH("*"&D2,A2:A5&"",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.

Many thanks. can you edit it abit more. I would a full barcode on column E then F the describtion

=INDEX($A$1:$A$1000,MATCH("*"&D1,$A$1:$A$1000&"",0)) is it correct?

I doubt why in the cell which has no data. the formula still can vlookup?
 
Last edited:
Upvote 0
Many thanks. can you edit it abit more. I would a full barcode on column E then F the describtion

=INDEX($A$1:$A$1000,MATCH("*"&D1,$A$1:$A$1000&"",0)) is it correct?
Maybe this...

=INDEX($F$1:$F$1000,MATCH("*"&D3,$E$1:$E$1000&"",0))

Still array entered!
 
Upvote 0
Maybe this...

=INDEX($F$1:$F$1000,MATCH("*"&D3,$E$1:$E$1000&"",0))

Still array entered!

No no. I don't mean that. I don't want to use vlookup for many sections. Just use once at column E then it auto comes F % G
 
Upvote 0
=INDEX($A$1:$A$1000,MATCH("*"&D1,$A$1:$A$1000&"",0))

why I cant make it to =INDEX(A:A,MATCH("*"&D1,A:A&"",0))?
 
Upvote 0
No no. I don't mean that. I don't want to use vlookup for many sections. Just use once at column E then it auto comes F % G
Sorry, I don't understand.

I'm logging off for the night. I'll check back tomorrow.
 
Upvote 0
Sorry, I don't understand.

I'm logging off for the night. I'll check back tomorrow.

ok.

I mean that I use the formula on column E. then the data come in column E-barcode and column F-describtion
I don't want to use on E then use on F. two times uses. I want to use once.

many thanks
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,954
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top