How to Retrieve PivotTable Values When Rows Change After Refresh
Question details
The user needs a reliable formula to extract data from a dynamically updating PivotTable where the sequence and number of items change after a refresh.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Populating a secondary summary table using data from a Power Query-backed PivotTable that dynamically changes its row count and order.
- Observed behavior
- Standard position-based formulas pull incorrect data because the physical row locations of specific data points (like cities) shift whenever the source data is refreshed.
Ensure your source data or PivotTable utilizes structured references (like a defined Table Name) so your lookup formulas can adapt to changing ranges without returning errors.
Use INDEX and MATCH to Lookup by Exact Labels
This is the most reliable method. It fetches values based on row and column headers instead of physical cell positions, making it immune to sorting or filtering changes.
By nesting the MATCH function inside the INDEX function, you force the spreadsheet to look for specific text (such as a city name) regardless of what row it currently occupies. The IFERROR wrapper ensures that if a city disappears from the dataset after a refresh, your summary table shows a zero instead of breaking.
Determine the cell in your destination table that contains the row label (e.g., $A2 for the city name) and the column header (e.g., B$1).
In your target cell, use a formula similar to this: =IFERROR(INDEX(Table1,MATCH($A2,Table1[City],0),MATCH(B$1,Table1[#Headers],0)), 0). Adjust 'Table1' and 'City' to match your actual structured table names.
If you need to sum two specific columns dynamically, you can add two INDEX/MATCH functions together: =IFERROR(INDEX(Table1,MATCH($A2,Table1[City],0),MATCH(B$1,Table1[#Headers],0)) + INDEX(Table1,MATCH($A2,Table1[City],0),MATCH(C$1,Table1[#Headers],0)), 0).
Drag the fill handle down to apply the formula to the rest of the rows in your destination table.
Use the SUMPRODUCT Function as an Alternative
If you are summarizing numerical data and prefer an array-based approach, SUMPRODUCT can tally up values where row criteria match.
Handle Dynamic PivotTables Flawlessly with WPS Spreadsheet
WPS Office provides robust data analysis capabilities, fully supporting dynamic arrays, advanced lookup functions (like INDEX and MATCH), and comprehensive PivotTable configurations to streamline your reporting.
- 1. Open your data file: Launch WPS Spreadsheet and open your existing .xlsx workbook containing the PivotTable.
- 2. Refresh your connections: Navigate to the Data tab and click 'Refresh All' to instantly update your PivotTable with the latest data.
- 3. Insert lookup formulas safely: Use the built-in Formula builder to easily construct your INDEX and MATCH functions, ensuring accurate data retrieval no matter how the row layout changes.
Frequently Asked Questions
Why shouldn't I just use standard cell references like =B5?
When a PivotTable refreshes, new items might be added, or existing items might be re-sorted. If 'New York' moves from row 5 to row 7, the formula =B5 will start pulling data for a different city, silently corrupting your final reports.
Why use structured table references (e.g., Table1[City]) instead of normal ranges?
Structured references automatically expand and contract as rows are added or removed from your source dataset. This means you won't have to manually update your formula ranges every time the data size changes.
Can I use GETPIVOTDATA instead of INDEX/MATCH?
Yes, GETPIVOTDATA is explicitly designed to retrieve data from a PivotTable based on item names. However, if an item disappears entirely after a refresh, GETPIVOTDATA will return a #REF! error. You must wrap it in IFERROR to handle dynamic, missing data smoothly.
How do I stop my column widths from resizing when I refresh the PivotTable?
Right-click anywhere inside the PivotTable, select 'PivotTable Options', and uncheck the box labeled 'Autofit column widths on update'. This will lock your layout in place.
