Function Problems

Excel Two-Way Lookup with Row and Column Criteria

Nimra MalikNimra Malik Nov 18, 2026 868 views

Question details

Find a value at the intersection of a temperature row and a matching column header using criteria on another worksheet.

Excel Two-Way Lookup Using Row and Column Criteria
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.
Before you start

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.

Solution 1Recommended

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.

1
Set up the criteria

On the TableLinksB36 worksheet, enter the temperature to find in cell A1 and the column header to find in cell B1.

2
Enter the lookup formula

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)).

3
Adapt the references

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.

4
Check the result

Press Enter and compare the returned value with the intersection of the matching temperature row and column header on the Lists worksheet.

Use INDEX and MATCH for a Two-Way Lookup
Exact matches: The 0 in each MATCH function requests an exact match. If no matching row label or header exists, the formula returns #N/A.
Two-way lookup in WPS

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. 1. Open the workbook: Open WPS Spreadsheet and select Open to load the workbook containing your lookup table.
  2. 2. Enter the criteria: On the criteria worksheet, enter the temperature in A1 and the matching column header in B1.
  3. 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. 4. Verify and save: Press Enter, verify the value against the source table, and select Save to keep the formula.
Find values using both row and column criteriaWork with Microsoft Excel XLS and XLSX filesUse familiar spreadsheet formulas across worksheetsOpen and edit spreadsheets with a free office suite
microsoft office alternative - wps office

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.