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

 

[norulen]

You can even use Vlookup function with wildcard character
for example in vlookup value try this "*" & Cell reference for the value you want to look for

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

 

[norulen]

May be if you post small sample of your input and output requirement i might be able to help you out with your second requirement

 

[norulen]

I just realized i am mistaken here. Wildcard Character can only be used for text and not numbers

You can even use Vlookup function with wildcard character
for example in vlookup value try this "*" & Cell reference for the value you want to look for

 

[Aladin Akyurek]

=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))?

You can. If the barcodes are in E and the 6-digit item to look for in D1...

Control+shift+enter, not just enter:

=VLOOKUP("*"&D1,$E$1:$E$100&"",1,0)

will return either the full barcode ending with the 6-digits of D1 or #N/A.

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

What do you mean by distribution and by using once?

=INDEX($F$1:$F$100,MATCH("*"&D1,$E$1:$E$100&"",0))

would return the F-value associated with the barcode which ends with the 6-digits of D1.

 

[Vanda_a]

You can. If the barcodes are in E and the 6-digit item to look for in D1...

Control+shift+enter, not just enter:

=VLOOKUP("*"&D1,$E$1:$E$100&"",1,0)

will return either the full barcode ending with the 6-digits of D1 or #N/A.



What do you mean by distribution and by using once?

=INDEX($F$1:$F$100,MATCH("*"&D1,$E$1:$E$100&"",0))

would return the F-value associated with the barcode which ends with the 6-digits of D1.

I mean when I use the vlookup on 6digit. I expect not only the 13digit return but also the description.

many thanks

 

[Aladin Akyurek]

=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))?

I mean when I use the vlookup on 6digit. I expect not only the 13digit return but also the description.

many thanks

Both in the same result cell?

 

[Aladin Akyurek]

I mean when I use the vlookup on 6digit. I expect not only the 13digit return but also the description.

many thanks

Both in the same result cell?

Like below...

Control+shift+enter, not just enter:

=VLOOKUP("*"&D1,$E$1:$E$100&"",1,0)&" "&INDEX($F$1:$F$100,MATCH("*"&D1,$E$1:$E$100&"",0))

 

[Vanda_a]

Like below...

Control+shift+enter, not just enter:

=VLOOKUP("*"&D1,$E$1:$E$100&"",1,0)&" "&INDEX($F$1:$F$100,MATCH("*"&D1,$E$1:$E$100&"",0))

not same cell. vlookup 6digit at E1, return the result at F1 then the description at G1. Is it possible?
and I doubt why the empty cell in E1 but the result still return at F1?

Many thanks

 

[Aladin Akyurek]

not same cell. vlookup 6digit at E1, return the result at F1 then the description at G1. Is it possible?
and I doubt why the empty cell in E1 but the result still return at F1?

Many thanks

Which cell contains the 6 digits we want to look for?

Which range contain the barcodes?

Which range contain the corresponding descriptions?

In which cell do you want the result barcode?

In which cell do you want the corresponding description?

 

[T. Valko]

not same cell. vlookup 6digit at E1, return the result at F1 then the description at G1. Is it possible?
and I doubt why the empty cell in E1 but the result still return at F1?

Many thanks

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