Keep Categories Aligned with Dynamic UNIQUE Results in Excel Using XLOOKUP
Question details
The user needs to keep manually entered categories correctly matched to payees when a UNIQUE formula dynamically changes the order of results.
- Product
- WPS Spreadsheets / Excel
- Device & OS
- not provided
- Scenario
- The user has a dynamic UNIQUE list of payees and a category column, but the categories do not follow when the payee list reorders.
- Observed behavior
- Manual category entries remain fixed in their original cells, causing them to become misaligned with the reordered UNIQUE payee list.
Create a two-column lookup table with all possible payees and their categories before applying the formula. Confirm that your WPS Spreadsheets or Excel version supports dynamic arrays and XLOOKUP; otherwise use the VLOOKUP alternative below.
Use XLOOKUP to Link Categories to a Dynamic UNIQUE Spill Range
Best when your version of WPS Spreadsheets or Excel supports dynamic arrays and XLOOKUP; it keeps categories matched automatically as the UNIQUE list order changes.
The key is to avoid typing categories beside the spilled UNIQUE results. Instead, point a lookup function at the spill range so categories are recalculated whenever UNIQUE reorders the payee list.
In a separate area or sheet, list each payee in one column and its matching category in the adjacent column. Define the payee range as payees and the category range as categories.
Click the cell where the dynamic payee list should start, type =UNIQUE(source_payees_range), and press Enter. The results spill into the cells below.
Next to the first cell of the UNIQUE results, enter =XLOOKUP(A3#,payees,categories) and press Enter. Replace A3# with the first cell of your UNIQUE spill range.
Change the source payee data or sort order, then verify that the UNIQUE list reorders and the category column follows the correct payee automatically.
Use VLOOKUP as a Compatibility Alternative for Category Matching
Use this if XLOOKUP is not available in your Excel or WPS Office version; it also retrieves categories dynamically from a lookup table.
Keep Payee Categories Aligned in WPS Spreadsheets with UNIQUE and XLOOKUP
WPS Spreadsheets supports dynamic array formulas and lookup functions, so you can build a live payee list and let categories follow automatically. Create your lookup table, apply UNIQUE and XLOOKUP, and keep your workbook accurate without manual re-sorting.
- 1. Open your workbook in WPS Spreadsheets: Launch WPS Office, open the workbook that contains your payee source data and the UNIQUE formula.
- 2. Create the payee-category lookup table: On the same sheet or a new sheet, list all payees in one column and their categories in the next column.
- 3. Apply the UNIQUE formula: Select the first cell for the dynamic payee list, enter =UNIQUE(source_range), and press Enter.
- 4. Link categories with XLOOKUP: In the category column next to the first UNIQUE result, enter =XLOOKUP(A3#,payees,categories) and press Enter.
- 5. Test the dynamic behavior: Change the source data or sort order, and confirm the UNIQUE list and its category column update together.
Frequently Asked Questions
Why do manually typed categories break when my UNIQUE list reorders?
Because the typed values stay in their original cells, while the spilled UNIQUE results change positions. Link them with a lookup formula such as =XLOOKUP(A3#,payees,categories) so each category is recalculated for the current payee order.
Does this method work in both WPS Spreadsheets and Microsoft Excel?
Yes. In versions that support dynamic arrays and XLOOKUP, use the same spill reference A3#. If XLOOKUP is not available, use VLOOKUP or INDEX/MATCH against the two-column lookup table instead.
What should I check if my XLOOKUP returns #N/A?
Open the lookup table and verify that each payee returned by UNIQUE exists exactly once in the payee column. #N/A appears when the payee is missing or the range names do not cover the full list.
Can I put the lookup table on another sheet?
Yes. Enter the payees and categories on a separate sheet, then refer to them with sheet names or define named ranges such as payees and categories. The XLOOKUP formula works the same way across sheets.
