Function Problems

How to Automatically Assign Excel Categories from Transaction Names

John WilsonJohn Wilson Oct 23, 2026 870 views

Question details

The user needs a way to automatically assign predefined categories and subcategories to bank or credit card transaction names in a spreadsheet.

How to Automatically Assign Excel Categories from Transaction Names
Product
Excel
Device & OS
not provided
Scenario
Importing monthly transaction statements and needing to automatically map vendor names (like UBER or UBER EATS) to specific expense categories.
Observed behavior
Transactions require manual categorization, and dependent drop-down lists are failing on new monthly sheets due to hard-coded references to a specific worksheet name.
Before you start

Ensure you have a complete list of unique transaction names from your recent bank statements and have planned out your desired categories before setting up the mapping table.

Solution 1Recommended

Create a Mapping Table and Use XLOOKUP

Build a dedicated mapping table and use the XLOOKUP function to automatically fetch categories based on the transaction name.

A mapping table acts as a reference library for your spreadsheet. By pairing it with the XLOOKUP function, Excel will automatically scan the transaction name and return the corresponding category and subcategory without manual data entry.

1
Create the Mapping Table

Open a new worksheet and name it 'Mapping'. Create three column headers: 'Transaction Name', 'Category', and 'Subcategory'. List your known merchants (e.g., UBER) and assign their respective categories.

2
Select the Target Cell

Navigate to your main transaction sheet and click the first empty cell in your 'Category' column.

3
Enter the XLOOKUP Formula

Type the formula =XLOOKUP(A2, Mapping!A:A, Mapping!B:B, "Uncategorized"), replacing A2 with the cell containing your transaction name. This tells Excel to find the name in the Mapping sheet and return the category.

4
Apply to All Rows

Press Enter, then click the bottom-right corner of the cell and drag the fill handle down to apply this formula to all your imported transactions. Repeat the process for the Subcategory column, changing the return array to Mapping!C:C.

Create a Mapping Table and Use XLOOKUP
Formula Tip: Using XLOOKUP is highly recommended over VLOOKUP because it handles exact matches by default and allows you to easily specify a 'not found' message like 'Uncategorized'.
Fast Data Categorization in WPS Office

Easily Categorize Transactions with WPS Spreadsheet

WPS Spreadsheet fully supports advanced modern functions like XLOOKUP and complex Data Validation, making it incredibly simple to build automated transaction trackers and mapping tables without compatibility issues.

  1. 1. Open Your Ledger: Launch WPS Spreadsheet and open your bank statement .xlsx file.
  2. 2. Set Up the Mapping Sheet: Create a 'Mapping' worksheet and populate it with your vendor names and corresponding categories.
  3. 3. Use XLOOKUP: In your main ledger, enter the =XLOOKUP() formula in the category column to automatically pull the assigned values from your Mapping sheet.
  4. 4. Configure Data Validation: Use the Data Validation tool under the 'Data' tab to set up relative-reference drop-down lists for any manual subcategory overrides.
Fully compatible with Microsoft Excel formulas and the .xlsx format.Seamless support for XLOOKUP, VLOOKUP, and advanced data validation.Lightweight software with a highly responsive data processing engine for large transaction lists.Built-in templates for personal finance and expense tracking available for free.
microsoft office alternative - wps office

Frequently Asked Questions

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

This error occurs if the transaction name in your ledger doesn't exactly match any name in your mapping table. You can fix this by either adding the missing merchant to your mapping table or utilizing the 'if_not_found' argument in your XLOOKUP formula to display a custom text like 'Review Needed'.

Can I use wildcard characters to match partial transaction names?

Yes, XLOOKUP supports wildcard matching. To do this, you must set the match_mode argument to 2 (wildcard match) and use asterisks (*) around your lookup value to match partial text strings, which is very useful for varying merchant names like '*UBER*'.

How do I make a drop-down list depend on another cell's value?

You can use the INDIRECT function within the Data Validation tool. First, create named ranges for your subcategories that perfectly match your main category names. Then, set your subcategory Data Validation source to =INDIRECT(Cell_Reference), where Cell_Reference is the cell containing the chosen main category.

Will my XLOOKUP formulas work if I save the file as an older Excel format?

XLOOKUP is a newer spreadsheet function and is not supported in older file formats like .xls (Excel 97-2003). To ensure your categorization formulas continue working properly, always save your document in the modern .xlsx format.