Manually categorizing hundreds of transactions is tedious. Automatic categorization saves hours of work each month.

Why Categorize Transactions

Budgeting

See where money goes:

How much on groceries?
Dining out spending?
Subscription costs?

Tax Preparation

Identify deductible expenses:

Business expenses
Medical costs
Charitable donations

Financial Analysis

Track trends:

Category spending over time
Compare to previous months
Identify areas to cut

Category Structure

Common Personal Categories

Category	Examples
Income	Payroll, interest, refunds
Housing	Rent, mortgage, repairs
Utilities	Electric, gas, water, internet
Groceries	Supermarkets, grocery stores
Dining	Restaurants, fast food, coffee
Transportation	Gas, parking, rideshare
Shopping	Amazon, retail stores
Entertainment	Movies, games, streaming
Healthcare	Doctor, pharmacy, insurance
Subscriptions	Netflix, Spotify, software
Transfers	Between accounts
Fees	Bank fees, ATM fees
Other	Uncategorized

Common Business Categories

Category	Examples
Revenue	Client payments, sales
Payroll	Salaries, contractor payments
Rent	Office space, equipment
Utilities	Business phone, internet
Software	SaaS subscriptions
Marketing	Ads, promotions
Travel	Flights, hotels, meals
Office Supplies	Supplies, equipment
Professional Services	Legal, accounting
Insurance	Business insurance
Taxes	Tax payments

Method 1: Keyword Matching with VLOOKUP

Create Keyword Table

Build a lookup table on a separate sheet:

Keywords sheet:

Keyword	Category
PAYROLL	Income
DIRECT DEP	Income
AMAZON	Shopping
WALMART	Groceries
TARGET	Shopping
STARBUCKS	Dining
UBER	Transportation
LYFT	Transportation
NETFLIX	Subscriptions
SPOTIFY	Subscriptions
SHELL	Transportation
EXXON	Transportation

VLOOKUP Formula

Find matching keyword and return category:

=IFERROR(VLOOKUP("*"&$B2&"*",Keywords!$A:$B,2,FALSE),"Uncategorized")

Problem: VLOOKUP doesn't do partial matching directly.

Better: INDEX/MATCH with SEARCH

=IFERROR(
  INDEX(Keywords!$B:$B,
    MATCH(TRUE,
      ISNUMBER(SEARCH(Keywords!$A:$A,$B2)),0)),
  "Uncategorized")

Enter as array formula (Ctrl+Shift+Enter) in older Excel.

Method 2: Nested IF Statements

Basic Structure

=IF(ISNUMBER(SEARCH("PAYROLL",B2)),"Income",
 IF(ISNUMBER(SEARCH("AMAZON",B2)),"Shopping",
 IF(ISNUMBER(SEARCH("STARBUCKS",B2)),"Dining",
 IF(ISNUMBER(SEARCH("SHELL",B2)),"Transportation",
 "Uncategorized"))))

Advantages

Simple to understand
Easy to modify
No separate lookup table needed

Disadvantages

Gets long with many keywords
Hard to maintain
Excel limits nesting depth (64 levels)

Method 3: SWITCH Function (Excel 2019+)

=SWITCH(TRUE,
  ISNUMBER(SEARCH("PAYROLL",B2)),"Income",
  ISNUMBER(SEARCH("AMAZON",B2)),"Shopping",
  ISNUMBER(SEARCH("STARBUCKS",B2)),"Dining",
  "Uncategorized")

Cleaner than nested IFs but same limitations.

Method 4: XLOOKUP with Wildcards (Excel 365)

=XLOOKUP("*"&"AMAZON"&"*",B:B,C:C,,"*")

Or search through keyword table:

=IFERROR(
  XLOOKUP(1,
    --ISNUMBER(SEARCH(Keywords!A:A,B2)),
    Keywords!B:B,
    "Uncategorized"),
  "Uncategorized")

Method 5: Google Sheets REGEXMATCH

=IF(REGEXMATCH(B2,"(?i)payroll|direct dep"),"Income",
 IF(REGEXMATCH(B2,"(?i)amazon|walmart|target"),"Shopping",
 IF(REGEXMATCH(B2,"(?i)starbucks|restaurant|grubhub"),"Dining",
 "Uncategorized")))

The (?i) makes it case-insensitive.

With Lookup Table

=IFERROR(
  FILTER(Keywords!B:B,
    REGEXMATCH(B2,Keywords!A:A)),
  "Uncategorized")

Building Your Keyword List

Start with Common Merchants

AMAZON → Shopping
WALMART → Groceries
TARGET → Shopping
COSTCO → Groceries
STARBUCKS → Dining
MCDONALD → Dining
UBER → Transportation
LYFT → Transportation
SHELL → Transportation
CHEVRON → Transportation

Add Your Specific Merchants

Review uncategorized transactions:

Landlord name → Housing
Employer name → Income
Local stores → Appropriate category

Handle Variations

Merchants appear differently:

AMAZON MKTPL
AMZN.COM
AMAZON PRIME
Amazon.com

Use partial keywords:

AMAZON → Shopping (catches all variations)

Handling Edge Cases

Same Merchant, Different Categories

Problem: Walmart could be Groceries or Shopping

Solutions:

Use more specific categories
Add secondary rules based on amount
Default to most common use, fix exceptions manually

Transfers Between Accounts

Identify by:

Keywords: TRANSFER, XFER, MOBILE DEPOSIT
Matching amounts (in and out)

=IF(ISNUMBER(SEARCH("TRANSFER",B2)),"Transfer",...)

Income vs Expense

Use amount sign:

=IF(C2>0,
  IF(ISNUMBER(SEARCH("PAYROLL",B2)),"Salary","Other Income"),
  <expense categorization>)

Improving Accuracy Over Time

Track Uncategorized

Filter for "Uncategorized" category:

Review descriptions
Add new keywords to table
Re-run categorization

Monthly Review Process

Run auto-categorization
Filter uncategorized
Add 5-10 new keywords
Re-run
Spot-check results

Accuracy Tracking

=COUNTIF(D:D,"Uncategorized")/COUNTA(D:D)

Goal: Under 10% uncategorized.

Sample Complete Solution

Setup

Sheet 1: Transactions

Date	Description	Amount	Category

Sheet 2: Keywords

Keyword	Category

Category Formula

In D2 of Transactions sheet:

=LET(
  desc,B2,
  keywords,Keywords!$A$2:$A$100,
  categories,Keywords!$B$2:$B$100,
  matches,ISNUMBER(SEARCH(keywords,desc)),
  result,FILTER(categories,matches,""),
  IF(result="","Uncategorized",INDEX(result,1))
)

(Excel 365 with LET function)

For Older Excel

=IFERROR(
  INDEX(Keywords!$B$2:$B$100,
    MATCH(TRUE,ISNUMBER(SEARCH(Keywords!$A$2:$A$100,B2)),0)),
  "Uncategorized")

Enter with Ctrl+Shift+Enter.

Summary

Automatic transaction categorization uses keyword matching to assign categories based on merchant names in descriptions. Build a keyword-to-category lookup table, then use formulas like INDEX/MATCH with SEARCH or nested IF statements. Start with common merchants, add your specific ones over time, and review uncategorized transactions monthly to improve accuracy. With a good keyword list, you can automatically categorize 80-90% of transactions, turning hours of manual work into minutes.

How to Categorize Bank Transactions Automatically