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

Question

I'm looking to match, with VLOOKUP, 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.

Here's an example worksheet: https://docs.google.com/spreadsheets/d/13QrS1nDjTN6oxIoXhvdyoiqAa4lf8x3eSY3566_eTJ8/edit?usp=sharing

1. The Expenses sheet has the data as we receive it, in columns A to C.
2. In Column D, we'd like to auto-assign a category to these expenses. If we do this with exact text in the column B, for example, BANK VISA PREAUTH PYMT, then it's easy. The VLOOKUP formula in column D works by getting this precise reference from the 'Categories' tab (cell A1 is worded exactly this way). This formula works: =IFERROR(VLOOKUP(B2,Categories!A:B,2,FALSE),"")
3. The challenge is that our expenses of a similar type won't always follow the exact text. For example: SEND E-TFR US***ke5 and SEND E-TFR US***xja. As such, we'd like to match just the text SEND E-TFR.

This means the VLOOKUP needs to match partials in the 'Categories' tab first column. How does adjust the VLOOKUP formula to achieve this?

PS. My question is quite similar to this one with the vital distinction of wanting to match substrings.
Categorize cells in Google Spreadsheets

EDIT: Answered.

Answer 1

Thanks to @Ruben for the answer. Eventually we decided to take a simpler approach. Just to check the regexp of the search string in VLOOKUP instead, and have those substrings in the key indexes in Categories tab.

So if an expense has this text in the cell B1:

SEND E-TFR US***ke5
SEND E-TFR US***abc
SEND E-TFR US***xyz
...

Which was causing issues with exact matches, we just truncate the string to the substring that occurs before the second space:

REGEXEXTRACT(B1, "[^\ ]*\ [^\ ]*")

To give us just this:

SEND E-TFR

And our renewed VLOOKUP function:

=IFERROR(VLOOKUP(TRIM(REGEXEXTRACT(B1, "[^\ ]*\ [^\ ]*")),Categories!A:B,2,FALSE), "")

The categories key index then refers to that truncated text for the lookup:

Which therefore matches and populates the right category:

A test file to see this working is: https://docs.google.com/spreadsheets/d/13QrS1nDjTN6oxIoXhvdyoiqAa4lf8x3eSY3566_eTJ8/edit?usp=sharing

Thanks!

Answer 2

In this case VLOOKUP can't be used because some of the categories kyes are contained within the data to be matched, by the other hand, the case is more complex than the description included in the body of the question as there are some categories that are a partial match between them (i.e. BANK VISA PREAUTH PYMT and Bank Visa) and because the matchs should be done case insensitive (BANK MUTUAL FUNDS should be categorized as Mutual Funds)

Here is the proposed formula

=ArrayFormula(ARRAY_CONSTRAIN(
SPLIT(TRIM(TRANSPOSE(QUERY(TRANSPOSE({
IF(
TRIM(B2:B9)=TRIM(TRANSPOSE(Categories!A1:A3)),Categories!B1:B3&"|",),
TRANSPOSE(IF(IFERROR(SEARCH(TRIM(Categories!A4:A8),TRANSPOSE(TRIM(B2:B9))),),Categories!B4:B8&"|",))
}),,1000000))),"|"),
COUNTA(B2:B9),1))

• B2:B9 are the cells having the values to be that should be compared with the categories keys.

  NOTE: It's assumend that there will not be blank cells in this range.

• Categories!A1:A3 are the cells containing the keys that should do a full match and Categories!B1:B3 are the corresponding categories

• Categories!A4:A8 are the cells conatining the keys that should do a partial match and Categories!B4:B8 are the corresponding categories