LOOKUP first 6 digits

poikl

Active Member
Can you please help? I have this "VLOOKUP(E2,CONT.xls!Prod,5,0" but I need that even if the Cel in CONT.xls Col. 5 has additional text or numbers after the first 6 matching digits it should still consider it a match as long as the first left 6 match

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
=VLOOKUP(E2&"*",CONT.xls!Prod,5,0)

if the first column of Prod consists text-formatted entries.

Thanks, It works

Is there a way to do Vlookup function to match a sequence inside the string? We use an 11 digit product identifier, but everyone on the floor only uses the middle four. all of our programs make us look up the full number before we can get info. The way I have been getting around this is to add a column, use the =Mid(b2,4,4) function, and lookup based on that. I have done this on all of my data sheets, and have to do this everytime I update.

Is there a way to do Vlookup function to match a sequence inside the string? We use an 11 digit product identifier, but everyone on the floor only uses the middle four. all of our programs make us look up the full number before we can get info. The way I have been getting around this is to add a column, use the =Mid(b2,4,4) function, and lookup based on that. I have done this on all of my data sheets, and have to do this everytime I update.

GrayMPA said:

Is there a way to do Vlookup function to match a sequence inside the string? We use an 11 digit product identifier, but everyone on the floor only uses the middle four. all of our programs make us look up the full number before we can get info. The way I have been getting around this is to add a column, use the =Mid(b2,4,4) function, and lookup based on that. I have done this on all of my data sheets, and have to do this everytime I update.
Hi GrayMPA:

Welcome to MrExcel Board!

Yes you can -- see the following ...
y031225h2.xls
BCDEF
2abc12345de110112k45*12k45*104
3abc12745de2102*12k45*104
4abc12645de3103104
5abc12k45de4104104
6abc12345de5105abc12k45de4104
7abc12345de6abc12k45de4104
Sheet6

You can use the wild cards * and ? in the lookup string

The alternative formulas in cells F2:F7 are ...

=VLOOKUP(E2,\$B\$2:\$C\$6,2,0)
=VLOOKUP(E3,\$B\$2:\$C\$6,2,0)
=VLOOKUP("*12k45*",\$B\$2:\$C\$6,2,0)
=VLOOKUP("*"&"12k45"&"*",\$B\$2:\$C\$6,2,0)
=VLOOKUP("*"&MID(E6,4,5)&"*",\$B\$2:\$C\$6,2,0)
=VLOOKUP("???"&MID(E7,4,5)&"*",\$B\$2:\$C\$6,2,0)

Thank you very much. I particularly like the last two options you offered, as I forgot to specify that string location was critical. Thanks for fixing my first question and then my (now unneeded) second one.

Bob

Replies
3
Views
73
Replies
5
Views
347
Replies
6
Views
396
Replies
6
Views
224
Replies
7
Views
297

1,203,485
Messages
6,055,685
Members
444,807
Latest member
RustyExcel

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.

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

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