Excel Two-Way Lookup Formula: Find a Value by Row and Column

An Excel two-way lookup formula finds a row by one label and a column by another, then returns the value where they intersect.

The classic INDEX and MATCH pattern works across many Excel versions, while newer functions can make the formula easier to read.

What is a two-way lookup in Excel?

A two-way lookup uses two criteria to locate one cell in a table: a row criterion and a column criterion.

For example, you might find a product’s sales for a selected month, an employee’s hours for a selected project, or a city’s population for a selected year.

Suppose cells A1:D4 contain this table:

If the product name is in F2 and the month is in G2, the formula should return the value at the intersection of that product’s row and that month’s column.

With F2 set to Pen and G2 set to February, the expected result is 180.

Use INDEX and MATCH for a two-way lookup

The most widely compatible approach combines INDEX with two MATCH functions:

=INDEX(B2:D4,MATCH(F2,A2:A4,0),MATCH(G2,B1:D1,0))

Here, INDEX returns a value from the data range B2:D4.

The first MATCH finds the position of the product in A2:A4, and the second finds the position of the month in B1:D1.

The final 0 in each MATCH requests an exact match.

For Pen and February, the first match returns row position 2 and the second returns column position 2.

INDEX therefore returns the value in the second row and second column of B2:D4: 180.

The ranges must line up: the data range should have the same number of data rows as the row-label range and the same number of data columns as the column-label range.

In this example, the labels are outside the data range, so the INDEX range begins at B2, while the month headers begin at B1.

Make the formula easier to fill or reuse

When copying the formula to other cells, lock the table ranges with dollar signs while leaving the input cells relative:

=INDEX($B$2:$D$4,MATCH(F2,$A$2:$A$4,0),MATCH(G2,$B$1:$D$1,0))

Now the lookup inputs can change as the formula is copied down, but the source table remains fixed.

You can also replace the ordinary ranges with Excel Table structured references when the data changes size frequently.

Structured references expand with the table, though the exact formula depends on the table and column names.

Use XLOOKUP for a readable alternative

In Microsoft 365 and Excel 2021 or later, nested XLOOKUP functions can express the same lookup by selecting the matching row first, then the matching column:

=XLOOKUP(F2,A2:A4,XLOOKUP(G2,B1:D1,B2:D4))

The inner XLOOKUP finds the chosen month header in B1:D1 and returns the corresponding data column from B2:D4.

The outer XLOOKUP finds the product in A2:A4 within that returned column.

With the example inputs, this returns 180.

This version can be easier to read once you understand the nesting, but it requires a version that supports XLOOKUP.

It is not available in Excel 2019 or Excel 2016.

For a workbook shared with users on older versions, INDEX and MATCH is often the safer choice.

Handle missing labels with a clear message

If either lookup label is absent, MATCH returns an error.

Wrap the INDEX and MATCH formula with IFERROR to display a useful message instead:

=IFERROR(INDEX($B$2:$D$4,MATCH(F2,$A$2:$A$4,0),MATCH(G2,$B$1:$D$1,0)),"Check product or month")

This catches any error produced by the lookup, including missing labels.

If you need to distinguish a missing product from a missing month, test each MATCH separately with IFNA or validate the input cells before running the full lookup.

A broad error handler can also hide unrelated problems, so use it when a friendly result is more useful than the underlying error.

Common issues and edge cases

• Extra spaces: A label that looks identical may contain leading or trailing spaces.
  
  Clean imported text with TRIM, or standardize the labels in the source and input cells.
• Numbers stored as text: The number 2026 and the text value "2026" may not match.
  
  Make the label and lookup value use the same data type.
• Duplicate labels: Exact MATCH returns the first matching label it finds.
  
  If row or column labels are duplicated, the result may not identify the intended record.
  
  Use unique labels or add a distinct identifier.
• Range size mismatch: If the data range and label ranges do not represent the same rows and columns, the formula can return the wrong intersection or an error.
  
  Check each range boundary carefully.
• Case sensitivity: MATCH and XLOOKUP comparisons are generally not case-sensitive.
  
  If uppercase and lowercase labels must count as different, a standard two-way lookup needs additional case-sensitive logic.
• Blank cells: A successful lookup can return a blank source cell, which may appear empty.
  
  That differs from a missing label, which produces a lookup error.

Does the formula work in Google Sheets?

Google Sheets supports the INDEX and MATCH pattern, so the same example formula works with the same cell ranges.

XLOOKUP is also available in Google Sheets, but spreadsheet function availability can change over time and may differ across Excel versions.

If a workbook must travel between platforms or older Excel installations, use the INDEX and MATCH version and confirm that your target spreadsheet supports the functions used.

For a reliable two-way lookup, keep row labels unique, use exact-match settings, and make the label ranges line up with the data grid.

Once those pieces are correct, changing either input cell retrieves a different intersection without rewriting the formula.