Use a Rules Table to Categorize Bank Transactions in Excel
Question details
The user needs to replace a complex, difficult-to-maintain IFS formula with a structured rules table to categorize bank transactions automatically.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Categorizing bank transactions based on keywords, reference fields, amounts, and specific combinations of criteria.
- Observed behavior
- The goal is to implement a formula that searches a rules table and returns the first matching transaction description, moving away from hardcoded, lengthy IFS logic.
Ensure you have a dedicated worksheet ready (e.g., named 'Filling') to store your categorization rules, target keywords, and expected output descriptions.
Build a Rules Table and Apply a Nested INDEX/MATCH Formula
Create a dedicated table for your rules and use an array formula combining IFNA, INDEX, MATCH, and SEARCH to automatically categorize transactions.
Instead of using a lengthy and error-prone IFS function, a dedicated rules table makes it much easier to maintain, update, and audit your categorization criteria.
The array formula evaluates the rules from top to bottom and returns the first match it finds. Because of this top-down execution, you must arrange your rules table so that highly specific rules are at the top, and broad, general 'catch-all' rules are placed at the bottom.
On a separate worksheet (e.g., 'Filling'), create columns for 'Description' (Column A), 'Required Field' (Column B), and 'Keyword' (Column C). Input your transaction rules row by row.
Order your rows so that the most specific keyword combinations are at the top of the table. General rules should be placed further down to prevent them from overriding the specific ones.
In your main bank transactions sheet, enter the nested lookup formula into the category column: =IFNA(INDEX(Filling!$A$2:$A$11,MATCH(1,ISNUMBER(SEARCH(Filling!$C$2:$C$11,$K2))*(Filling!$B$2:$B$11=$F2),0)), IFNA(INDEX(Filling!$A$2:$A$11,MATCH(TRUE,ISNUMBER(SEARCH(Filling!$C$2:$C$11,$H2)),0)), ...))
Update the cell ranges (e.g., Filling!$A$2:$A$11) and the target cells ($K2, $F2, $H2, etc.) in the formula so they match the actual column layout and size of your specific workbook.
Automate Transaction Categorization with WPS Spreadsheet
WPS Spreadsheet offers powerful formula calculation, including advanced lookup and array functions like INDEX, MATCH, and SEARCH, allowing you to effortlessly categorize thousands of bank transactions with a simple rules table.
- 1. Download and Install WPS Office: Visit the official WPS website, download the free software suite, and install it on your device.
- 2. Open your Bank Transactions: Launch WPS Spreadsheet and open your financial workbook (.xlsx or .csv) containing the bank exports.
- 3. Set up the formula: Create your rules table on a new sheet and paste your nested INDEX and MATCH formula into the transactions sheet. WPS Spreadsheet natively handles array formulas to automatically categorize your rows.
Frequently Asked Questions
Why does the formula return the wrong category for some transactions?
Because the formula stops searching at the first match it finds in your rules table. If a general keyword (like 'Transfer') is placed higher than a specific keyword (like 'Transfer to Savings Account'), the general rule will trigger first. Ensure your most specific rules are at the very top of the table.
Can I use this rules table method to categorize by transaction amounts as well?
Yes. You can expand the logic in the MATCH array using the multiplication (*) operator to check for numerical ranges. For example, adding '*(Transactions!$E2>100)' into the array will force the rule to match only if the text keyword is found and the amount is over 100.
What happens if a bank transaction matches none of the rules in the table?
The formula will return an #N/A error. You can handle this gracefully by wrapping your entire formula in a final IFNA or IFERROR function that outputs 'Uncategorized' or 'Needs Review'. This allows you to easily filter and add new rules for those specific transactions.
Will using complex array formulas slow down my workbook?
Array formulas can impact performance if they are calculating over massive datasets. To optimize calculation speed, always restrict your rules table references to the exact number of necessary rows (e.g., $A$2:$A$50) rather than referencing entire columns (e.g., A:A).
