Replace Content of a cell based on a list of options in Google Sheets

1
votes

For years I have been using a very long formula to help me achieve what I am going to ask here.

Basically, every month I download my Credit Card statement and use a bunch of formulas to sum it up in a better way to import into my accounting software. What my formulas do is that it reads the Description in each row and based on the content of that row it rewrites that description.

For example: In my credit card it may say "Starbucks Main St.", so it looks for that information in a list and if it finds "Starbucks", it will rewrite (in the cell where the formula is located) to "Coffee". So, what I have is:

Column B > Original Description from Credit Card Statement
Column F > Keyword to be found
Column G > New description

The problem is that I use an IF Search formula that has to be as long as the size of that list. It works, but I wonder if it can't be simplified. Another "issue" is that I can't use an arrayformula, so putting the formula on top of the spreadsheet and it would expand to the whole statement.

Here's an example: https://docs.google.com/spreadsheets/d/14255Sz28ItSMZ32EebIFiAHQFxtyCJakN5q3gYUB-N0/edit#gid=0

ANY ideas?

1
regexgoogle-sheetsgoogle-sheets-formulaarray-formulasgs-vlookup
While going thru other questions here, I found a formula to at least use Regexmatch to find the keyword in the list and it says if its true or false (the match). So that's one step, now I need to use that match to replace the text. I added that to the spreadsheet on Column E. =ARRAYFORMULA(REGEXmatch(LOWER(B2:B); LOWER(TEXTJOIN("|"; 1; G2:G))) - Bebeto Le Garfs
Well one more step forward. I managed to use array formula with regexmatch, and replicated the formula I originally used. it is still long, but at least I can use array formula on it and don't need to copy the formula down to all rows. - Bebeto Le Garfs

1 Answers

1
votes

try:

=ARRAYFORMULA(PROPER(IFNA(VLOOKUP(REGEXEXTRACT(PROPER(B2:B); 
 SORT(TEXTJOIN("|"; 1; PROPER(G2:G)); 1; 0)); G2:H; 2; 0); B2:B)))

0