Function Problems

How to Automatically Categorize Bank Transactions in Excel

Maira Mehtab Sep 20, 2026 868 views

Question details

The user needs to automatically assign account categories to imported bank transactions based on previously entered transactions or a customized mapping table.

Product: Excel
Device & OS: not provided
Scenario: Organizing and categorizing imported financial or bank transaction data without manually typing categories for each row.
Observed behavior: Transactions are currently imported without assigned categories, requiring a lookup formula method to map transaction descriptions to their correct account names automatically.

Before you start

Ensure your bank transactions are imported into a structured spreadsheet format and that you have a list of common transaction descriptions ready to link to their corresponding categories.

Solution 1Recommended

Use a VLOOKUP Formula with a Mapping Table

Create a dedicated lookup table to automatically match transaction descriptions with their corresponding categories using the VLOOKUP function.

This approach requires creating a reference table that maps specific keywords or exact descriptions from your bank statement to your preferred budget categories. Once set up, a simple formula can categorize thousands of rows in seconds.

Create a mapping table

Create a new sheet or select an empty range (for example, Columns K and L). In the first column (K), type your common bank transaction descriptions. In the second column (L), type the corresponding category name (e.g., 'Groceries' or 'Utilities').

Add a category column

Navigate to your main bank transactions sheet and add a new column header named 'Category' next to your transaction descriptions.

Enter the VLOOKUP formula

Click the first empty cell under your new Category column. Assuming your transaction description is in cell C2, type the formula =VLOOKUP(C2, K:L, 2, 0) and press Enter.

Apply the formula to all rows

Select the cell with the formula, click and hold the small square at the bottom-right corner of the cell (the fill handle), and drag it down to the end of your transaction data.

Exact Match Requirement: The '0' at the end of the VLOOKUP formula ensures it only returns an exact match. If transaction descriptions vary slightly from month to month, you may need to update your mapping table or use wildcards to capture partial text matches.

Manage Finances Seamlessly

Easily Categorize Your Bank Data in WPS Office

WPS Spreadsheet provides powerful data analysis tools, including VLOOKUP, XLOOKUP, and PivotTables, to effortlessly manage and categorize your personal or business bank transactions for free.

1. Open WPS Spreadsheet: Launch WPS Office and open the workbook containing your imported bank transactions.
2. Set Up a Mapping Sheet: Create a new worksheet dedicated to mapping your transaction identifiers to their assigned categories.
3. Apply Lookup Formulas: Use the VLOOKUP function in your main data sheet to automatically pull categories from the mapping sheet.
4. Analyze Your Spending: Highlight your categorized data and use the PivotTable feature under the Insert tab to quickly summarize your monthly spending.

Fully compatible with Microsoft Excel (.xlsx) formatsSupports advanced lookup functions like VLOOKUP and XLOOKUPIncludes built-in templates for financial tracking and budget managementLightweight, fast, and user-friendly interface

microsoft office alternative - wps office

Frequently Asked Questions

Why is my VLOOKUP formula returning an #N/A error?

The #N/A error occurs when the formula cannot find an exact match for the transaction description in the lookup table. Ensure there are no extra spaces or hidden characters in your data, and verify that the exact description exists in your mapping table.

Can I categorize transactions based on partial text matches?

Yes, you can use wildcards within your VLOOKUP formula. For example, using the formula =VLOOKUP("*"&C2&"*", K:L, 2, 0) will search for the text string anywhere within the lookup column, which is helpful if bank descriptions include varying dates or store numbers.

Is there a better alternative to VLOOKUP for categorizing data?

If you are using a newer version of Excel or WPS Spreadsheet, XLOOKUP is highly recommended. It defaults to an exact match, handles errors natively without needing IFERROR, and can look up data in any direction.

Excel & Data Problems