I'm looking to match, part of the words in a column in the main sheet to an index of keywords/categories where partial words are stored for ease in another sheet.
I've already learned that I can't achieve that with VLOOKUP so I'm looking for alternatives but am a bit stuck.
Here's an example worksheet: https://docs.google.com/spreadsheets/d/1RUgSQAtWMXm2tq8o_ytWpzndt6q8jrnEsx5u9vhg5BU/edit?usp=sharing
The Month 1 sheet has the data as we receive it, in columns A to D. In Column D, we'd like to auto-assign a category to these expenses. As several Vendors have seemingly no clear system. The challenge is that our expenses of a similar type won't always follow the exact text. For example: Coop-4249 Dietlikon IndusDietlikon 000 and Coop-1412 ZH Stauffacher Zürich 000 or Migros MMM Zürich City Zürich CHE and Migros Hauptbahnhof Zürich 000. As such, we'd like to match just the text Coop or Migros, so far i've tried to do it with the wildcard function Coop and Migros as the word we look for is not always the first in the description.
PS. My question is quite similar to this one with the vital distinction of wanting to match substrings that are not always the first word:
Can you help me?