2

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.

2
  • 1
    In other words, for "QUIKSILVER - Visa Purchase" should the result cell be empty? Or you want see "Not found"? For empty result you can try =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)
    – JohnSUN
    Jun 7, 2023 at 10:26
  • Wow, yes that's exactly what I'm looking for! I don't think I can mark it as answered as it's a comment, but thanks very much. I'll have to do a bit of research of the different functions to understand what you did! Ta
    – Ellipsis
    Jun 7, 2023 at 10:44

1 Answer 1

0

Assuming you have the first table in D1:E and the second table in A1:B. Try the following formula in B2:

=ARRAYFORMULA(IF(A2:A="",,IFNA(VLOOKUP(REGEXEXTRACT(A2:A,"(?i)"&TEXTJOIN("|",1,D2:D)),D2:E,2,),"Not found")))

Your Answer

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

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