Vlookup - Find partial match from the lookup_value

[Clayton18]

Hi All!

Im trying to see if its possible to use vlookup to find partial matches with the lookup value

For example:

The lookup value is a long string of data - i'm trying to match the return array to each value, however my lookup array only displays one part of the value.

Thankyou!

 

[StephenCrump]

Like this perhaps?

	A	B	C	D	E
1
2	12322	Pink		12345 - Australia - 93847	Grey
3	5555	Yellow		34643 - 5555 - London	Yellow
4	93847	Grey		12322 - Paris - 1111	Pink
5				12322 - Blah - 5555	Pink,Yellow
6				111 - New York -222	n/a
Sheet1

Cell Formulas
Range	Formula
E2:E6	E2	=TEXTJOIN(",",,(FILTER(B$2:B$4,ISNUMBER(FIND(A$2:A$4,D2)),"n/a")))

 

[Peter_SSs]

For the future , I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

Not sure if you can have data like rows 6 & 7 below but this is my suggestion. In some circumstances it has a couple of differences in results (inclusions and order) compared to Stephen's formula, which I have included below for comparison.

24 03 01.xlsm
	A	B	C	D	E	F
1
2		Peter	Stephen
3	12345 - Australia -93847	Grey	Grey		12322	Pink
4	34643 - 5555 - London	Yellow	Yellow		5555	Yellow
5	12322 - Paris - 1111	Pink	Pink		93847	Grey
6	5555 - XXX - 12322	Yellow, Pink	Pink,Yellow
7	55556 - XXX - 12322	Pink	Pink,Yellow
8	12345 - Australia -93848		n/a
Lookup

Cell Formulas
Range	Formula
B3:B8	B3	=TEXTJOIN(", ",1,XLOOKUP(TRIM(TEXTSPLIT(A3,"-")),E$3:E$5,F$3:F$5,""))
C3:C8	C3	=TEXTJOIN(",",,(FILTER(F$3:F$5,ISNUMBER(FIND(E$3:E$5,A3)),"n/a")))