Bank Statement Lesson 3: Categorise Every Transaction With a Keyword Table

📎 This article includes 1 downloadable practice file ↓

📘 Bank Statement Automation Course · Lesson 3 of 8

Instead of tagging 70 rows by hand, keep a two-column Rules table: a keyword and its category. One formula looks up the first keyword that appears in each narration.

The formula

=IFERROR(INDEX(Rules!$B$2:$B$14,
         MATCH(TRUE, ISNUMBER(SEARCH(Rules!$A$2:$A$14, B2)), 0)),
         "Other")

SEARCH looks for every keyword in the narration at once and returns a position or an error. ISNUMBER turns that into TRUE/FALSE, MATCH(TRUE,…) finds the first hit, and INDEX returns its category. No match: “Other”.

Improve it every month

1. Filter the Category column for “Other”.
2. Add a keyword for each new payee to Rules.
3. Within two or three months almost nothing is “Other”.

Turn Rules into an Excel Table (Ctrl+T) and use Rules[Keyword], so new rows are picked up automatically.

Common mistakes

• Short keywords like “LIC” also match “PUBLIC”. Use longer, specific text.
• Blank rows in the Rules range: SEARCH(“”) matches everything. Keep the range tight or use a Table.

Practice

Download the workbook below. The statement is made up but follows real Indian bank formats (NEFT, NACH, UPI narrations). The Check column turns green when your formula gives the right answer.