CODEWITHSUNDEEP
regexgoogle-sheetsgoogle-sheets-formulaarray-formulasgs-vlookup
Divine Cosmos
Divine Cosmos

Reputation: 329

How to match cell to lookup table?

I am trying to set up an expense tracker. I copy-paste my credit card transactions directly in Google Sheets. I want to categorize them into say grocery, restaurant, gas, movies, etc. I want to do this using some sort of lookup table which I can keep adding to in the future.

The lookup table could look something like:

A          B
cafe       restaurant
cuisine    restaurant
restaurant restuarant
gas        gas
amc        movies
safeway    grocery
costco     grocery

Now, the copy-paste from my credit card statement looks something like this:

A                   B       
AMC20 BILL          12      
RRCAFE              20      
COSTCOPAYMNET       80      
ARCOGAS             30      
THE CUISINE BLOCK   20

The final outcome that I want is this:

A                   B       C
AMC20 BILL          12      movies
RRCAFE              20      restaurant
COSTCOPAYMNET       80      grocery
ARCOGAS             30      gas
THE CUISINE BLOCK   20      restaurant

I tried looking at VLOOKUP and MATCH but couldn't figure out a way to do this reverse lookup.

Upvotes: 1

Views: 146

Answers (1)

player0
player0

Reputation: 1

=ARRAYFORMULA(IF(LEN(D1:D), 
 VLOOKUP(REGEXEXTRACT(LOWER(D1:D), TEXTJOIN("|", 1, A1:A)), A:B, 2, 0), ))

0

Upvotes: 1

Related Questions