Use Structured References to Find Table Values by Name or Region in WPS Office Spreadsheets
Question details
The user needs to retrieve a specific sales value by person and calculate regional averages using Excel Table structured references in WPS Office Spreadsheets instead of fixed cell addresses.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has a sales table named Table1 with Sales Person, Sales Amount, and Region columns and wants to look up Dan's sales and average sales for regions beginning with East.
- Observed behavior
- The formulas should remain reliable when table rows are sorted or inserted, without relying on fixed cell references.
Make sure your data is formatted as a WPS Spreadsheets table and named Table1, or replace Table1 in the formulas below with your actual table name.
Retrieve values by name and region with XLOOKUP and FILTER structured references
Use this formula-based method to return a person's sales amount and average a region group dynamically.
Structured references use the table name and column header instead of A1-style ranges, so the formulas adjust automatically when rows are added, removed, or sorted.
The FILTER function is combined with LEFT to match regions that begin with “East” and return only matching sales amounts to AVERAGE.
Click an empty cell and enter =XLOOKUP("Dan",Table1[Sales Person],Table1[Sales Amount],"Not found") to return Dan's sales amount.
Click another empty cell and enter =AVERAGE(FILTER(Table1[Sales Amount],LEFT(Table1[Region],4)="East",0)) to average sales for regions beginning with East.
Create a PivotTable to summarize sales by person and region
Use this alternative when you prefer an interactive summary without writing lookup formulas.
Use structured references to find table values by name or region in WPS Office Spreadsheets
Use WPS Office Spreadsheets to enter XLOOKUP and FILTER formulas with Excel Table structured references, so sales lookups and regional averages update automatically without adjusting fixed cell addresses.
- 1. Open your workbook in WPS Office Spreadsheets: Launch WPS Office, open the .xlsx workbook that contains your sales table, and click inside the table.
- 2. Confirm the table name and headers: Select the table, go to the Table Tools tab, and verify the table is named Table1 with column headers Sales Person, Sales Amount, and Region.
- 3. Enter the lookup formula: Click an empty cell and type =XLOOKUP("Dan",Table1[Sales Person],Table1[Sales Amount],"Not found") to return Dan's sales amount.
- 4. Enter the regional average formula: Click another empty cell and type =AVERAGE(FILTER(Table1[Sales Amount],LEFT(Table1[Region],4)="East",0)) to average East region sales.
- 5. Sort or add rows to confirm: Add or sort table rows and confirm the structured references recalculate automatically.
Frequently Asked Questions
What formula returns Dan's sales amount from a named table in WPS Spreadsheets?
Use =XLOOKUP("Dan",Table1[Sales Person],Table1[Sales Amount],"Not found"). If your table has a different name, replace Table1 with the actual table name.
How do I calculate the average sales for regions beginning with East?
Use =AVERAGE(FILTER(Table1[Sales Amount],LEFT(Table1[Region],4)="East",0)). This filters to region names starting with East and averages the matching sales amounts.
Do structured references still work after sorting or inserting rows?
Yes. Structured references refer to the entire table column, so WPS Spreadsheets adjusts the formula range automatically when rows are sorted, added, or deleted.
Can I use a PivotTable instead of formulas to summarize sales by region?
Yes. Click inside the table, go to Insert > PivotTable, then drag Region to Rows and Sales Amount to Values to create a regional summary without formulas.
Do I need to rename my table to Table1 for these formulas to work?
No. Table1 is only an example. Replace Table1 in the formulas with the name shown on the Table Tools tab for your WPS Spreadsheets table.
