Lookup part of the string to match with other

[indygo]

hi guys,

I'm trying to identify product by ID and for some weird reason it doesn't work with either vlookup or xlookup.

Description:
Column A has part of the ID that refers to the Name in Column B.
I want to Vlookup those values to see their corresponding names in C2,D2,E2 and so on (green text).


When I'm trying to put in C2: =vlookup(E2,$A$1:$B$173,2,TRUE) I'm getting the same result for every single column which is "Banana". The problem is that I'm looking up longer string with shorter one.
Obviously the formula should compare two ID's and lookup corresponding name but it doesn't work. Any ideas please?


hlookup doesn't work either.

ID	Name	0_0C732313111	654100_21212121124	654101_1111444122s
		Banana	Apple	Pear
0_	Banana
654100	Apple
654101	Pear

 

[jorismoerings]

Hi,

can be solved but only if your product id's are consistent.
The first part of the full product code (till "_"shows the ID) if that's consistent throughout your table only VLOOKUP that part.
Be aware: to create consistency in the ID table i've removed "_" from cell A3
You could try something like this:

Book1
	A	B	C	D	E
1	ID	Name	0_0C732313111	654100_21212121124	654101_1111444122s
2			Banana	Apple	Pear
3	0	Banana
4	654100	Apple
5	654101	Pear
Sheet1

Cell Formulas
Range	Formula
C2:E2	C2	=VLOOKUP(VALUE(LEFT(C$1,SEARCH("_",C$1,1)-1)),$A$1:$B$5,2,0)

 

[indygo]

Legend! thanks for sorting it out for me!

 

[jorismoerings]

Legend! thanks for sorting it out for me!

np - glad i could help