Use an Excel Formula to Return a Row and Column Intersection
Question details
Return the value at the intersection of a selected row and column in a worksheet.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A worksheet contains a table of values and cells used to select a row and column, similar to a multiplication table.
- Observed behavior
- The desired result is the table value where the selected row and column meet.
Identify the row labels, column headers, and data values in your table, along with the cells containing your selections. The example below uses row labels in A2:A9, column headers in B1:K1, and data in B2:K9.
Use INDEX and MATCH to Look Up Both Selections
Use this formula when the row and column are selected by their labels.
MATCH finds the position of each selected label, and INDEX returns the value at the corresponding position in the data range. The row-label range must align with the data rows, and the header range must align with the data columns.
Enter a row label in N4 and a column header in N3. Both must match labels in the example table.
Select the cell where you want the result, enter =INDEX($B$2:$K$9,MATCH($N$4,$A$2:$A$9,0),MATCH($N$3,$B$1:$K$1,0)), and press Enter.
Edit the ranges and selection-cell references in the formula bar to match your worksheet. If the table is on another sheet, prefix its ranges with the sheet name, such as Sheet2!$B$2:$K$9.
To display a blank when a selection is missing or unmatched, use =IFERROR(INDEX($B$2:$K$9,MATCH($N$4,$A$2:$A$9,0),MATCH($N$3,$B$1:$K$1,0)),"") in the result cell.
Find Table Intersections in WPS Spreadsheet
Use INDEX and MATCH in WPS Spreadsheet to return the value where a selected row and column meet.
- 1. Open the workbook: In WPS Spreadsheet, choose Open and select your spreadsheet file.
- 2. Identify the table: Find the row labels, column headers, data range, and cells containing the two selections.
- 3. Enter the lookup formula: Select an empty result cell and enter =INDEX($B$2:$K$9,MATCH($N$4,$A$2:$A$9,0),MATCH($N$3,$B$1:$K$1,0)), replacing the references with those in your worksheet.
- 4. Check the result: Press Enter, then change the selections in the row and column input cells to verify the returned value.
Frequently Asked Questions
Why does the formula return #N/A?
One of the selections may not match its label range. Check the values in N4 and N3 against A2:A9 and B1:K1, including extra spaces and whether numbers are stored as text.
How do I return a blank when a selection is missing?
Wrap the lookup in IFERROR, for example =IFERROR(INDEX($B$2:$K$9,MATCH($N$4,$A$2:$A$9,0),MATCH($N$3,$B$1:$K$1,0)),""). This also hides other formula errors.
Can the table be on another worksheet?
Yes. Add the sheet name to each table range, for example Sheet2!$B$2:$K$9. Use single quotes around sheet names containing spaces, such as 'Raw Values'!$B$2:$K$9.
What happens if a row or column label appears more than once?
MATCH with 0 returns the position of the first exact match. Use unique labels if you need to select a specific row or column.
