How to Return a Table Value Using X and Y Coordinates in WPS Spreadsheets
Question details
The user needs a formula to look up the value at the intersection of a row heading and column heading in a 25-by-25 Excel table.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has a 25-by-25 table with x and y headings and wants to retrieve the intersecting value by entering two headings.
- Observed behavior
- A formula is needed to locate the row by one heading, the column by another heading, and return the value in the intersecting cell.
Before writing the formula, confirm that the row headings are in the first column and the column headings are in the first row of the data range. Use unique headings so the MATCH functions return the intended row and column.
Use INDEX with two MATCH functions to return the intersection value
Use this formula when both the row and column positions are determined by heading values rather than fixed positions.
The INDEX function returns a value from a range, while MATCH finds the position of a lookup value. Combining them lets you move from two input cells, such as D29 and D28, to the intersecting cell in a 25-by-25 table.
In WPS Spreadsheets, click the cell where you want the intersecting value to appear.
Type =INDEX($B$2:$Z$26,MATCH(D29,$A$2:$A$26,0),MATCH(D28,$B$1:$Z$1,0)) and press Enter. D29 is the row heading lookup cell, D28 is the column heading lookup cell, $A$2:$A$26 contains row headings, and $B$1:$Z$1 contains column headings.
Change the values in D29 and D28 to confirm that the formula returns the corresponding intersection from the table.
Return a Value at the Intersection of X and Y Headings in WPS Spreadsheets
Use the built-in INDEX and MATCH functions in WPS Spreadsheets to return the value where a row heading and column heading meet. This approach is compatible with Excel formulas and works with .xlsx, .xls, and .csv files without extra add-ins.
- 1. Open your workbook: Open the workbook in WPS Spreadsheets.
- 2. Click the result cell: Click the cell where you want the formula result to appear.
- 3. Enter the lookup formula: Type =INDEX($B$2:$Z$26,MATCH(D29,$A$2:$A$26,0),MATCH(D28,$B$1:$Z$1,0)).
- 4. Press Enter and test: Press Enter and change D29 or D28 to confirm that the returned value updates correctly.
- 5. Save the file: Save the file in .xlsx format to keep compatibility with Excel.
Frequently Asked Questions
Why does the formula return #N/A after I enter it?
This usually means one of the MATCH functions cannot find the lookup value. Check that the row heading exists in $A$2:$A$26, the column heading exists in $B$1:$Z$1, and both values are the same data type. Use 0 as the third MATCH argument for an exact match.
Can I type text directly into the MATCH function instead of using D29 and D28?
Yes. For text headings, wrap them in double quotes inside the formula. However, using input cells such as D29 and D28 makes the formula easier to update without editing the formula itself.
Does this formula work when the table is on another sheet?
Yes. Include the sheet name in the references, such as Sheet2!$B$2:$Z$26. Click the sheet and select the range to let WPS Spreadsheets build the reference automatically.
What happens if there are duplicate row or column headings?
MATCH with 0 returns the first match in the lookup range. To avoid unexpected results, make the row and column headings unique or add a helper column with a unique ID.
