How to Find a Matching Value Across Sheets and Return a Range in WPS Office Spreadsheets
Question details
The user needs to look up a key value across several worksheets and return the associated range from the worksheet where the match is found.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A workbook contains multiple sheets with the same layout. The user wants to enter a lookup key and have WPS Spreadsheets return the matching data block from the correct sheet without checking each sheet manually.
- Observed behavior
- The matching key can be located on one of several sheets, and the goal is to return that sheet's associated range automatically.
Check that each worksheet stores the lookup key in the same cell and the return range in the same location. Note the exact sheet names; if a name contains spaces, wrap it in single quotes in the formula, for example 'Q1 Sales'!$C$3.
Return the range from the matching sheet with a nested IF formula
Best for WPS Office versions that do not support LET/VSTACK or for workbooks that must stay compatible with classic Excel formulas.
This formula compares the lookup key in A1 with each sheet's key cell, such as C3. When a match is found, it returns the associated range, such as D3:G6, from that sheet. If your version supports IFS, you can write the same logic with IFS instead of nested IF.
Open the workbook in WPS Office Spreadsheets, go to the summary sheet, and select a range the same size as the return block—for example, select B3:E6 if the source range is D3:G6.
Type =IF(Sheet2!$C$3=$A$1,Sheet2!$D$3:$G$6,IF(Sheet3!$C$3=$A$1,Sheet3!$D$3:$G$6,IF(Sheet4!$C$3=$A$1,Sheet4!$D$3:$G$6,IF(Sheet5!$C$3=$A$1,Sheet5!$D$3:$G$6,"Not found")))) and replace the sheet names, key cells, and return ranges with your actual references.
Press Enter if WPS supports dynamic arrays and fills the selected range. If the result does not fill the whole range, press Ctrl+Shift+Enter to enter it as a classic array formula.
Change the value in A1 to another key and confirm the returned range now comes from the sheet whose key cell matches.
Use a single dynamic-array formula with LET, VSTACK, and XLOOKUP
Use this when working in Microsoft 365 or a WPS build that supports LET, VSTACK, XLOOKUP, and LAMBDA; it is the most concise way to return the range from the correct sheet.
Find a matching value across sheets and return a range with WPS Office Spreadsheets
Use WPS Office Spreadsheets to return the associated range from the correct worksheet with a compact nested IF formula—no Microsoft 365-only functions required. It keeps your workbook compatible with Excel formats and is free to use.
- 1. Open your workbook in WPS Office Spreadsheets: Launch WPS Office, click the WPS Spreadsheets icon, and open the workbook that contains the multiple sheets.
- 2. Select the destination range: On the summary sheet, select a block the same size as the range you want to return, such as B3:E6 for D3:G6.
- 3. Enter the cross-sheet formula: Type =IF(Sheet2!$C$3=$A$1,Sheet2!$D$3:$G$6,IF(Sheet3!$C$3=$A$1,Sheet3!$D$3:$G$6,IF(Sheet4!$C$3=$A$1,Sheet4!$D$3:$G$6,"Not found"))) and replace the references with your sheets.
- 4. Press Enter or Ctrl+Shift+Enter: Press Enter to spill the result if your WPS version supports dynamic arrays; otherwise press Ctrl+Shift+Enter to output the full range.
- 5. Check the result with a different key: Change the lookup key in A1 and verify the returned range switches to the sheet whose key cell matches.
Frequently Asked Questions
How do I make the formula return the whole D3:G6 range instead of a single value?
Select a destination range the same size as the source range and use Ctrl+Shift+Enter if your WPS version does not spill dynamic arrays. The nested IF formula returns the full range from the matching sheet.
Can I use this formula when the key cell is not C3 on every sheet?
Yes, update the key-cell reference in each IF condition, for example change $C$3 to the actual key cell, and adjust the return range to match the data block you want.
What should I do if my sheet names contain spaces?
Wrap each sheet name in single quotes, such as 'Q1 Sales'!$C$3 and 'Q1 Sales'!$D$3:$G$6, so WPS reads the reference correctly.
Will the Microsoft 365 LET/VSTACK/XLOOKUP formula work in WPS Office?
It only works if your WPS Office version supports LET, VSTACK, XLOOKUP, and LAMBDA. If these functions are not available, use the recommended nested IF method.
What happens when no sheet has a matching key?
The final IF argument returns the text 'Not found'. You can replace it with an empty string or any custom message.
