How to Automatically Update a VLOOKUP Range After Power Query Refresh in WPS Spreadsheets
Question details
The user needs a lookup range in WPS Spreadsheets to automatically expand and contract when Power Query refreshes the source data, instead of using a fixed range such as $B$2:$D$335244.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user refreshes a Power Query output whose returned row count changes and wants lookup formulas to continue referencing the full result set automatically.
- Observed behavior
- A fixed lookup range such as $B$2:$D$335244 remains static after refresh, causing formulas to miss new rows, include empty rows, or create unnecessary calculation overhead.
Confirm that your Power Query output is loaded to a worksheet and identify the columns used for the lookup value and return value. Make sure the query is set to refresh and that the results are not currently placed inside a fixed named range that you plan to replace.
Load Power Query results into a table and use structured references in lookup formulas
Use this method when your query output row count changes after refresh. The table expands and contracts automatically, so VLOOKUP, XLOOKUP, or INDEX/MATCH always use the current result set.
A fixed range such as $B$2:$D$335244 cannot adjust to the new number of rows after Power Query refresh. Loading the query results as a table makes the lookup refer to the table object rather than a hard-coded address, which also reduces unnecessary calculation overhead.
In WPS Spreadsheets, open the workbook that contains the Power Query output and click the worksheet where the query results are loaded.
Select any cell inside the Power Query results, go to the Insert tab, click Table, confirm the range, and enable My table has headers if applicable. If you are creating or editing the Power Query load destination, choose Table instead of a worksheet range.
After conversion, go to the Table Tools Design tab or use the Name Box and set a meaningful table name, such as LookupData, for easier formula entry.
In the lookup formula, replace $B$2:$D$335244 with the table name and column reference. For example, use =VLOOKUP(A2,LookupData,2,FALSE), =XLOOKUP(A2,LookupData[LookupColumn],LookupData[ReturnColumn]), or =INDEX(LookupData,MATCH(A2,LookupData[LookupColumn],0),3).
Click Data > Refresh All or the Power Query refresh command, add or remove source rows, and confirm that the table and lookup results update without editing the formula range.
Create a dynamic named range for the lookup data
Use this only if you cannot convert the query output to a table and need a formula-based dynamic range. Note that OFFSET-based ranges are volatile and can be slower on large data.
Use WPS Spreadsheets to Keep VLOOKUP Ranges Dynamic After Power Query Refresh
Load Power Query results into a WPS Spreadsheets table and switch lookup formulas to structured references so the range expands and contracts automatically after every refresh.
- 1. Open your workbook in WPS Spreadsheets: Download and open WPS Office, then open the Excel workbook that contains the Power Query output.
- 2. Load the query results as a table: Select the Power Query output cells, go to Insert > Table, and enable My table has headers.
- 3. Change the lookup range to table references: Use the table name and structured column references in VLOOKUP, XLOOKUP, or INDEX/MATCH instead of a fixed range such as $B$2:$D$335244.
- 4. Refresh and save in Excel-compatible format: Click Data > Refresh All to verify the range adjusts, then save the workbook as .xlsx to keep compatibility with Microsoft Excel.
Frequently Asked Questions
Will VLOOKUP work with a table structured reference in WPS Spreadsheets?
Yes. You can use the table name instead of a fixed range, for example =VLOOKUP(A2,LookupData,2,FALSE). WPS Spreadsheets treats the table as a named object and updates its row count on refresh.
How do I stop Power Query from outputting to a fixed range?
When loading the query, choose Table as the destination. If the results are already loaded, select a cell in the output and convert it by going to Insert > Table or Home > Format as Table.
Can I use XLOOKUP or INDEX/MATCH with a Power Query table in WPS?
Yes. Use structured columns such as LookupData[LookupColumn] and LookupData[ReturnColumn] in XLOOKUP or INDEX/MATCH. WPS Spreadsheets supports these lookup functions in saved .xlsx workbooks.
Why does my fixed lookup range still show old rows after refresh?
A fixed address like $B$2:$D$335244 does not resize after refresh. Convert the data to a table and reference the table so row count changes are included automatically.
Does converting Power Query output to a table cause extra calculation overhead?
No. It usually reduces overhead because formulas only reference the actual table rows rather than a large fixed range.
