Search code examples
regexgoogle-sheetsgoogle-sheets-formulaarray-formulasgs-vlookup

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

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?

Solution

  • try:

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

    0