Vlookup not using unique search values

[wombleuk]

I have a list of several thousand product codes and prices in two columns as below

15513DU1TTDunlopDU SP STREETRESPONSE 79T 100.46
15513MN1TTMichelinMN ENERGY E3B GX 79T 103.54
1556514AV5TTAvonAV ZT5 75T 106.62
1556514DU2TTDunlopDU STREETRESPONSE 2 75T 109.77
1556514MN3TTMichelinMN ENERGY E3B 75T 112.78
1655015BR1VVBridgestoneBR RE040 73V>> 115.86
1656014AV3HHAvonAV ZV3 75H 118.94

the supplier often changes part of the code usually the make or pattern so using vlookup to find the code and associated price will not always work. Due to the quantity I cannot do this manually.

I have a list of stockcodes which is the first set of characters (between 7 to 17 characters) which does not change.
15513DU1T
15513MN1T
1556514AV5T
1556514DU2T
1556514MN3T
1655015BR1V
1656014AV3H

My question is, is there anyway I can use my master stockcode (short version) list to search within the product list and return the associated value??

I hope I have explained it well enough
thanks
David

[Melvinrobb]

would using a wildcard in the vlookup work?
=vlookup(a2&"*",......

[wombleuk]

Melvin
thanks for that, I have tried using a wildcard before but it is knowing where to put it and how to use it, and I simple didn't know how
But your info worked brilliantly
thank you

[elizabethgbecker]

I have a related question. I have rows of unique data and want to look up the value in the row, then return value from same row, next column. The column of the value being looked up changes.

Ex:
Row 1: Name: Jim Email: j@example.com Phone:xxxxxxx
Row 2: Name: Jane About her: Jane xxxx Email: jane@example.com Phone:xxxxx

What I need is a way to find "Email" and "Phone" and return value of column same row, one spot to the right.

Sorry if not posting in right place, not sure where to post. Thanks!

[Melvinrobb]

You need to start a new post with this question. I would upload a sample workbook with some mock data, showing an example of what you would want the formulas to return in a specific instance.
i am confident you will get a quick response with a new post.