How to Automatically Assign Excel Categories from Transaction Names
Question details
The user needs a way to automatically assign predefined categories and subcategories to bank or credit card transaction names in a spreadsheet.

- 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.
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.
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.
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.
Navigate to your main transaction sheet and click the first empty cell in your 'Category' column.
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.
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.

Update Dependent Drop-Down Lists with Relative References
Fix broken dependent drop-down lists on new monthly sheets by removing hard-coded sheet references in the Data Validation settings.
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. Open Your Ledger: Launch WPS Spreadsheet and open your bank statement .xlsx file.
- 2. Set Up the Mapping Sheet: Create a 'Mapping' worksheet and populate it with your vendor names and corresponding categories.
- 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. 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.

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.




