2

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.

2 Answers 2

3

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:

Truncated text to check

Which therefore matches and populates the right category:

Correct match for substring

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

Thanks!

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

6
  • Thank you for this. But just to clarify, in my example sheet Categories tab, once I know the partial match idea, we will only have the partial text in column 1. So we will not have these two as keys: BANK VISA PREAUTH PYMT and Bank Visa. We will just have Bank Visa. Basically in that file the rows in orange would remain. Would this help in making the arrayformula a bit simpler? Thanks so much for the effort!
    – PKHunter
    Nov 25, 2020 at 8:13
  • Btw, the formula itself as it doesn't work. Gives a "REF" error. Anyway it's too finite in how it's using the specific columns of an example file. We'll need it to be usable without perpetually using column references as we add more Categories. So referring to entire columns is desirable, thanks!
    – PKHunter
    Nov 25, 2020 at 10:25
  • Thematically in terms of pseudocode I was thinking that before invoking VLOOKUP I could just get a substring of the search value until the second space found in the phrase. So if the value is BANK VISA PREAUTH PYMT, then I'd truncate that to BANK VISA and use that as a search key. Which means the "B2" reference in this formula -- =IFERROR(VLOOKUP(B2,Categories!A:B,2,FALSE),"") -- would need to change to a substring of B2. Would this work?
    – PKHunter
    Nov 25, 2020 at 10:29
  • 1
    @PKHunter Please post your final formula as an answer. Technically you also should edit your question and the demo spreadsheet.
    – Rubén
    Nov 25, 2020 at 13:42
  • 1
    Done. Thanks for your pointer! Love this site and helpful people like you.
    – PKHunter
    Nov 25, 2020 at 18:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.