I’m lost and not finding the correct formula.
I have a sheet in Excel that looks like this:
[CODE] – [CATEGORY] – [SUBCATEGORY] – [DETAILS]
And I have a 2nd Excel sheet with a column [DESCRIPTION].
I want now to add the category/subcategory/details from sheet 1, based on the description column in my table from sheet 2. The [CODE] from sheet 1 can be found as part of multiple descriptions from the description column in sheet 2.
Example:
Sheet 1:
| CODE | CATEGORY | SUBCATEGORY | DETAILS |
|---|---|---|---|
| ABC | CAT 1 | SUBCAT 1.1 | DETAILS1 |
| DEF | CAT 2 | SUBCAT 2.1 | DETAILS2 |
In sheet 2 in [DESCRIPTIONS]:
“ABC DEF”
So this description field matches up with 2 codes: so the codes from both lines can be found in the description (wildmatch search).
Now I want to enrich in sheet 2, after the description field, I want to have a new column called “Categories” that will contain “ABC | DEF” so it should concatenate ALL the matches that it finds.
How can I achieve this?
I tried a lot with the formulas as mentioned in the title but I cannot get it to work…