LOOKUP first 6 digits

[poikl]

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

 

[Aladin Akyurek]

=VLOOKUP(E2&"*",CONT.xls!Prod,5,0)

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

 

[poikl]

Thanks, It works

 

[GrayMPA]

Follow up question.

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]

Follow up question.

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.

 

[Yogi Anand]

Follow up question.

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
	B	C	D	E	F
2	abc12345de1	101	12k45	*12k45*	104
3	abc12745de2	102		*12k45*	104
4	abc12645de3	103			104
5	abc12k45de4	104			104
6	abc12345de5	105		abc12k45de4	104
7	abc12345de6			abc12k45de4	104
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)

 

[GrayMPA]

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