How to Return Data from Another Sheet Using INDEX MATCH in WPS Spreadsheets
Question details
The user needs to retrieve a value from a master worksheet into a subsidiary worksheet by matching multiple criteria with INDEX and MATCH.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user maintains a master worksheet and needs to return the corresponding value to a subsidiary worksheet when two or more criteria match.
- Observed behavior
- A single-criterion lookup may not be sufficient, and the formula may need array entry depending on the spreadsheet version.
Open the workbook that contains both the master and subsidiary worksheets. Identify the return column and the two or more criteria columns in each sheet, and confirm that the lookup ranges use the same number of rows.
Use an INDEX MATCH Array Formula with Multiple Criteria
Use this formula when you need a compact, formula-only lookup from a master sheet based on two or more conditions.
The INDEX function returns the value from the column you specify, while MATCH finds the relative row position. Multiplying criteria inside MATCH creates an array of 1 and 0 values so only rows where all conditions are true produce a 1.
Open the subsidiary worksheet in WPS Spreadsheets and select the cell where the matching value should appear.
Type =INDEX(MasterSheet!$C$2:$C$100,MATCH(1,(A2=MasterSheet!$A$2:$A$100)*(B2=MasterSheet!$B$2:$B$100),0)). Replace MasterSheet, A2, B2, and column C with your sheet name, criteria cells, criteria columns, and return column.
If your version does not calculate the formula automatically, press Ctrl+Shift+Enter instead of Enter. WPS Spreadsheets adds curly braces around the formula when it is an array formula.
Drag the fill handle down to apply the same lookup to other rows, keeping absolute ranges for the master data.
Create a Helper Column to Avoid an Array Formula
Use this approach if you prefer a simpler non-array formula and can add a column to the master worksheet.
Use WPS Spreadsheets to Look Up Data Across Sheets with INDEX MATCH
Use WPS Spreadsheets to return data from another sheet with the same INDEX MATCH array formula workflow as Excel. You can open the master workbook, create the formula on the subsidiary sheet, confirm it with Ctrl+Shift+Enter if needed, and save it back in Excel format.
- 1. Download WPS Office: Download WPS Office from the official WPS website and install it on your computer.
- 2. Open the workbook: Open the Excel workbook that contains the master and subsidiary worksheets in WPS Spreadsheets.
- 3. Enter the INDEX MATCH formula: Select the subsidiary sheet cell and enter =INDEX(MasterSheet!$C$2:$C$100,MATCH(1,(A2=MasterSheet!$A$2:$A$100)*(B2=MasterSheet!$B$2:$B$100),0)).
- 4. Confirm the array formula: Press Ctrl+Shift+Enter if WPS Spreadsheets does not calculate it automatically, then check that the matching value appears.
- 5. Save the workbook: Save the workbook in Excel format so the formula and results remain compatible with Microsoft Excel.
Frequently Asked Questions
Can WPS Spreadsheets use INDEX MATCH to get data from a different sheet?
Yes. In the subsidiary sheet, enter =INDEX(MasterSheet!$C$2:$C$100,MATCH(1,(A2=MasterSheet!$A$2:$A$100)*(B2=MasterSheet!$B$2:$B$100),0)) and press Ctrl+Shift+Enter if required.
Why must I press Ctrl+Shift+Enter for this INDEX MATCH formula?
The multiplication creates an array formula. In versions that do not support dynamic arrays, pressing Enter alone may return #VALUE! or an incorrect result. Ctrl+Shift+Enter tells WPS Spreadsheets or Excel to evaluate the array, and curly braces appear around the formula.
How can I return data from another sheet using more than two criteria?
Add another condition inside MATCH: =INDEX(MasterSheet!$D$2:$D$100,MATCH(1,(A2=MasterSheet!$A$2:$A$100)*(B2=MasterSheet!$B$2:$B$100)*(C2=MasterSheet!$C$2:$C$100),0)). Confirm it as an array formula.
Why does my INDEX MATCH formula return #N/A even though the value exists?
Check that the criteria cells match the master sheet formatting, the lookup ranges have the same number of rows, and no extra spaces are present. Use absolute references for the master data ranges.
Does this formula work in both Microsoft Excel and WPS Spreadsheets?
Yes. The same INDEX MATCH formula works in both applications, and WPS Spreadsheets lets you open and save Excel workbooks for free.
