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
- The Expenses sheet has the data as we receive it, in columns A to C.
- 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),"")
- The challenge is that our expenses of a similar type won't always follow the exact text. For example:
SEND E-TFR US***ke5
andSEND E-TFR US***xja
. As such, we'd like to match just the textSEND 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.