I have a bunch of transaction descriptions, and I'd like to check against an array for any matches (case insensitive, in any position within the cell) and return the associated position within the array when successful.
I'm looking to use an array of text values and search a cell for each value within the array, returning the position within the array as the "found" result, on Google Sheets.
For example, I have a budget spreadsheet, with a series of transactions, like:
COLES - Visa Purchase
WWW.ALHGROUP.COM* ME&U - Visa Purchase
MY BUNNINGS - Visa Purchase
ALH VENUES - Visa Purchase
ALDI STORES - Visa Purchase
QUIKSILVER - Visa Purchase
ALDI STORES - Visa Purchase
COLES EXPRESS - Visa Purchase
and I have a named range ("categories") of
| search_val | return_cat |
|---|---|
| Woolworths | Groceries |
| Coles | Groceries |
| ALH | Pub |
| Aldi | Groceries |
| Bunnings | Home |
So, basically I want Google Sheets to search my transaction list for any of the items within search_val (not case sensitive, and in any position of the cell - so similar to an iferror(search(search_val,A1)) and return the position within the array so I can lookup the return_cat. Is this possible?
So I'd end up with
| transaction | return_cat |
|---|---|
| COLES - Visa Purchase | Groceries |
| WWW.ALHGROUP.COM* ME&U - Visa Purchase | Pub |
| ALH VENUES - Visa Purchase | Pub |
| MY BUNNINGS - Visa Purchase | Home |
| ALDI STORES - Visa Purchase | Groceries |
| QUIKSILVER - Visa Purchase | Not found |
| ALDI STORES - Visa Purchase | Groceries |
| COLES EXPRESS - Visa Purchase | Groceries |
Many thanks! My first time in WebApps, let me know please if you think this belongs in another exchange.
=ArrayFormula(TEXTJOIN(",",1,IF(REGEXMATCH(LOWER(A1),LOWER($F$2:$F$6)),$G$2:$G$6,"")))(if table search_val/return_cat is in range F1:G6)