How to Use a Two-Way INDEX MATCH Formula for Transposed Data in WPS Spreadsheets
Question details
The user needs to convert horizontal source data into a transposed table using a two-way INDEX MATCH formula that also checks a Yes column, without modifying the original workbook data.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has source data in a vertical range and wants to populate a transposed summary table where row and column criteria are matched, using a Yes column as a condition.
- Observed behavior
- The goal is to return matching values into the transposed layout without changing or restructuring the original source range.
Before entering the formula, verify that the row criteria are in column G, the column headers start at H1, and the source data occupies A2:C26. If your version of WPS Spreadsheets does not support dynamic arrays, be ready to press Ctrl+Shift+Enter to enter the formula as an array formula.
Use the Two-Way INDEX MATCH Array Formula in WPS Spreadsheets
Use this formula-only method to return values from C2:C26 where the row criteria match G2 and the column criteria match H1, without changing the source data.
The formula multiplies two logical conditions—$A$2:$A$26=$G2 and $B$2:$B$26=H$1—to create an array of 1 and 0, and MATCH finds the first row where both are true. The final &"" suppresses zero results and displays an empty string instead.
Click cell H2, the top-left cell of your transposed results table.
Type =INDEX($C$2:$C$26,MATCH(1,($A$2:$A$26=$G2)*($B$2:$B$26=H$1),0))&"" and press Enter. In WPS Spreadsheets versions without dynamic arrays, confirm with Ctrl+Shift+Enter.
Use the fill handle to drag the formula from H2 across the header row and then down through the result rows.
Create a Refreshable Result Table with Power Query
Use this alternative if you are working with a dynamic source table and want to update results by refreshing instead of copying formulas.
Build a Two-Way Transposed Lookup in WPS Spreadsheets
Use WPS Spreadsheets to enter the INDEX MATCH array formula, fill it across and down, and produce a transposed summary table from your existing data without changing the original range.
- 1. Open your workbook in WPS Spreadsheets: Open the source workbook and click the sheet where you want the transposed table.
- 2. Enter the lookup criteria: Place the row criteria in G2 and the column headers starting in H1 so the formula has stable references.
- 3. Apply the two-way INDEX MATCH formula: In H2, enter =INDEX($C$2:$C$26,MATCH(1,($A$2:$A$26=$G2)*($B$2:$B$26=H$1),0))&"" and confirm with Ctrl+Shift+Enter if needed.
- 4. Fill the results table: Drag the fill handle across and down to populate the transposed table from the source data.
- 5. Save and reuse: Save the workbook as .xlsx to keep compatibility with Excel and WPS Office.
Frequently Asked Questions
Why does the formula return 0 instead of a blank when no match exists?
The &"" at the end of the formula converts the result to text and replaces zero values with an empty string. Keep it included if you want blanks instead of zeros.
Do I need Ctrl+Shift+Enter for this formula in WPS Spreadsheets?
In WPS Spreadsheets versions that do not support dynamic arrays, yes—select the formula cell, click in the formula bar, and press Ctrl+Shift+Enter. In Microsoft 365 or Excel 2021, the formula can be entered normally.
Can I change the source range beyond row 26?
Yes. Replace $C$2:$C$26, $A$2:$A$26, and $B$2:$B$26 with your actual range, and keep the same absolute references so the formula fills correctly.
How do I add more conditions besides the Yes column?
Add another logical check inside the MATCH multiplication, for example multiply by ($D$2:$D$26="Yes"), and keep all range sizes identical.
Is there a non-formula way to update the transposed table?
Yes, if your version of WPS Spreadsheets supports Power Query, you can build a query from the source table, shape the criteria, and right-click the output table to Refresh.
