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:

Categorize text in Google Sheets based on matching partial text in the Category index

Can you help me?

  • Welcome to Web Applications. Please show the formulas that you tried.
    – Rubén
    Mar 14 at 20:50
  • @Rubén i've tried the regex formula from the linked article: =IFERROR(VLOOKUP(TRIM(REGEXEXTRACT(B1, "[^\ ]*\ [^\ ]*")),Categories!A:B,2,FALSE), "") However if I understand it correctly, it crops the ends which is not what i'd like. I'd like the 'lookup' to look at the full cell in categories and match a category once it finds a match.
    – Dlinius
    Mar 15 at 7:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.