Excel Two-Way Lookup with Row and Column Criteria
Question details
Find a value at the intersection of a temperature row and a matching column header using criteria on another worksheet.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A table on one worksheet contains temperature row labels and column headers, while lookup criteria are entered on another worksheet.
- Observed behavior
- The user wants a formula that returns the value at the intersection of the matching row and column.
Identify the result table, its temperature labels, and its column headers. Note the cells containing the row and column criteria, and make sure the label ranges align with the rows and columns of the result table.
Use INDEX and MATCH for a Two-Way Lookup
Use this formula to match a temperature row and a column header, then return their intersecting value.
On the TableLinksB36 worksheet, enter the temperature to find in cell A1 and the column header to find in cell B1.
Select the cell where you want the result and enter =INDEX(Lists!$B$2:$M$100,MATCH(TableLinksB36!$A$1,Lists!$A$2:$A$100,0),MATCH(TableLinksB36!$B$1,Lists!$B$1:$M$1,0)).
Replace the Lists and TableLinksB36 sheet names and cell ranges with your own. Keep the row labels aligned with the result table's rows and the headers aligned with its columns.
Press Enter and compare the returned value with the intersection of the matching temperature row and column header on the Lists worksheet.

Find Table Intersections in WPS Spreadsheet
Use INDEX and MATCH in WPS Spreadsheet to look up a temperature row and column header and return their intersecting value.
- 1. Open the workbook: Open WPS Spreadsheet and select Open to load the workbook containing your lookup table.
- 2. Enter the criteria: On the criteria worksheet, enter the temperature in A1 and the matching column header in B1.
- 3. Add the formula: Select an empty result cell and enter =INDEX(Lists!$B$2:$M$100,MATCH(TableLinksB36!$A$1,Lists!$A$2:$A$100,0),MATCH(TableLinksB36!$B$1,Lists!$B$1:$M$1,0)), replacing the sheet names and ranges as needed.
- 4. Verify and save: Press Enter, verify the value against the source table, and select Save to keep the formula.

Frequently Asked Questions
Can I use the same cell for both lookup criteria?
Yes. If the same value should match both the row label and the column header, use the same criteria cell reference in both MATCH functions.
Why does the formula return #N/A?
With 0 as the third MATCH argument, both criteria must match exactly. Check that the temperature exists in the row-label range and the header exists in the header range, and check for text-versus-number differences or extra spaces.
What happens if a row label or column header appears more than once?
MATCH with an exact-match argument of 0 returns the position of the first matching item. The formula therefore uses the first matching row and first matching column.
Can the lookup table and criteria be on different sheets?
Yes. Prefix each range or criteria cell with its worksheet name, such as Lists!$A$2:$A$100 or TableLinksB36!$A$1. Enclose a sheet name in single quotes if it contains spaces.




