Extracting Exact word match with a pre-defined list into an new corresponding column

Question

Good day,&nbsp;I have spend probably too long trying to figure out this problem. In Sheet 1 there is a list of Brands:BRANDRUWAGZEBRAEASIGASAFROXLIBERTYMTSIn Sheet two I have a list of products with brands located in various positions in the text string:PRODUCT DESCRIPTIONZebraLABELS 100MM X 30MM C40 GREENEASIGAS &nbsp;GAS LP 19KG&nbsp;AFROX&nbsp;CYLINDER / DEPOSIT 19KGSCREW EYE LIBERTY NO.04 BPSCREW EYE EIFEL NO.04 BPMTS&nbsp;BOLT ANCHOR 10X75 16MM DRTHREADED ROD GALV 20MM RUWAG&nbsp;The problem comes in when I need to populate the brands matching in Sheet 1 in Column B of Sheet 2 containing the Products next to the corresponding product. The desired outcome should look like this:PRODUCT DESCRIPTIONBRANDZebraLABELS 100MM X 30MM C40 GREENZEBRAEASIGAS &nbsp;GAS LP 19KGEASIGAS&nbsp;AFROX&nbsp;CYLINDER / DEPOSIT 19KGAFROXSCREW EYE LIBERTY NO.04 BPLIBERTYSCREW EYE EIFEL NO.04 BPNo MATCHMTS&nbsp;BOLT ANCHOR 10X75 16MM DRMTSTHREADED ROD GALV 20MM RUWAGRUWAG&nbsp;How ever the formula to do this is eluding me. Any ideas?

riny_van_eekelen · Answer

DaanvdM&nbsp;Have a look at the attached file. I used structured tables. Then the location of them doesn't matter.&nbsp;

harun24hr · Answer

DaanvdM&nbsp;If you are on Microsoft-365 then can try below formula.=FILTER($A$2:$A$7,ISNUMBER(SEARCH($A$2:$A$7,D2)),"No Match")For array approach can try BYROW() lambda function.=BYROW(D2:D8,LAMBDA(x,FILTER($A$2:$A$7,ISNUMBER(SEARCH($A$2:$A$7,x)),"No Match")))&nbsp;&nbsp;

daanvdm · Answer

Thank you Riny_van_Eekelen&nbsp; and&nbsp; Harun24HR&nbsp;@. Looks like the formula of Riny_van_Eekelen&nbsp;is the closest to our desired outcome. There is, however, a snag. The formula matches partial matches as well. Let's say the description is Easigas and there are both the Easi and Easigas brands. It matches the first from the top, not necessarily the best match

riny_van_eekelen · Answer

DaanvdM&nbsp;That's quite a collection of descriptions and brand names you have there. Many don't seem to be related to any brand name to begin with. Like "EXPANSION BOLT 5X50MM" or "EYELET BRASS".&nbsp;I've played around a bit with PowerQuery and fuzzy matching, but the end result isn't very promising. With a rather low threshold, PQ matches just under 3000 thousand (out of 18000+) descriptions and many of them are obviously very wrong.Can you explain what you actually want to get out of this exercise?

Forum Discussion

Extracting Exact word match with a pre-defined list into an new corresponding column

4 Replies

Resources

PRODUCT DESCRIPTION	BRAND
ZebraLABELS 100MM X 30MM C40 GREEN	ZEBRA
EASIGAS GAS LP 19KG	EASIGAS
AFROX CYLINDER / DEPOSIT 19KG	AFROX
SCREW EYE LIBERTY NO.04 BP	LIBERTY
SCREW EYE EIFEL NO.04 BP	No MATCH
MTS BOLT ANCHOR 10X75 16MM DR	MTS
THREADED ROD GALV 20MM RUWAG	RUWAG