ThisCalculator

Everyday

Two Way Lookup with INDEX and MATCH

Updated · Standard formula, shown below

This article explains how to perform a two-way lookup in Excel using the INDEX and MATCH functions, which lets you find a value at the intersection of a specific row and column. You enter a row lookup value and a column lookup value, and the formula returns the corresponding cell from a table. This is a more flexible alternative to VLOOKUP when your data isn’t arranged in a simple left-to-right layout.

Why INDEX and MATCH Instead of VLOOKUP

VLOOKUP can only search in the first column of a range and return a value to the right. That limitation forces you to rearrange your data or use complex workarounds when the lookup column isn’t on the left. INDEX and MATCH, by contrast, work in any direction: you can look up values to the left, to the right, above, or below the result column. The pair also handles column insertions gracefully—if you add a column between the lookup and result columns, a VLOOKUP formula breaks, but an INDEX/MATCH formula that references the exact result column keeps working.

Another advantage is speed. MATCH only searches one row or column, not the entire table, so on large datasets it can be noticeably faster than VLOOKUP with a full-table range. And because INDEX returns a reference, you can use it in other formulas, like SUM or AVERAGE, without needing a helper cell.

How MATCH Works

MATCH returns the position of a lookup value within a single row or column. Its syntax is:

MATCH(lookup_value, lookup_array, [match_type])

  • lookup_value – the value you’re searching for (can be a number, text, or cell reference).
  • lookup_array – the row or column range to search.
  • match_type – 0 for exact match, 1 for less than or equal to (requires sorted ascending), -1 for greater than or equal to (requires sorted descending). For most lookups you’ll use 0.

For example, MATCH("North", A2:A10, 0) returns the row number where “North” appears in A2:A10. If “North” is in row 5 of that range, it returns 4 (because A2 is row 1 of the range). This position number is what you feed into INDEX.

How INDEX Works

INDEX returns the value at a given row and column position within a range. Its syntax is:

INDEX(array, row_num, [column_num])

If you give it a single row or column, you can omit the other number. For example, INDEX(A1:C10, 4, 2) returns the value in row 4, column 2 of A1:C10 (which is B4). The row and column numbers are relative to the range you specify, not the worksheet.

The Two-Way Lookup Formula

To find a value at the intersection of a given row label and column label, combine two MATCH functions inside one INDEX:

=INDEX(data_range, MATCH(row_label, row_lookup_range, 0), MATCH(column_label, column_lookup_range, 0))

Here’s a concrete example. Suppose you have a table in A1:D4:

      A        B      C      D
1            Q1     Q2     Q3
2  North     100    110    120
3  South     90     95     98
4  East      80     85     88

To get the Q2 value for South, you would use:

=INDEX(B2:D4, MATCH("South", A2:A4, 0), MATCH("Q2", B1:D1, 0))

MATCH(“South”, A2:A4, 0) returns 2 (South is the second row in A2:A4). MATCH(“Q2”, B1:D1, 0) returns 2 (Q2 is the second column in B1:D1). INDEX(B2:D4, 2, 2) then returns the value at row 2, column 2 of B2:D4, which is 95.

Common Mistakes and How to Fix Them

  • Mismatched range sizes – The row lookup range (e.g., A2:A4) must have the same number of rows as the data range (B2:D4). Similarly, the column lookup range (B1:D1) must have the same number of columns. If not, INDEX will return a #REF! error.
  • Using match_type 1 or -1 without sorting – If you use 1 or -1, the lookup array must be sorted in the correct order. For exact matches, always use 0 to avoid surprises.
  • Text mismatch – Extra spaces, non-printable characters, or different text cases can cause MATCH to fail. Use TRIM and CLEAN to tidy up data, or use EXACT if case matters.
  • #N/A error – This means MATCH didn’t find the lookup value. Double-check that the value exists and that the range is correct.
  • Absolute vs relative references – When copying the formula, make sure the lookup ranges are locked with dollar signs ($) so they don’t shift.

When to Use a Two-Way Lookup

Use this pattern whenever you need to find a value based on two criteria that form a row and column header. Common examples include:

  • Finding a price based on a product name and a region.
  • Looking up a score based on a student name and a test date.
  • Retrieving a sales figure based on a salesperson and a month.
  • Getting a rate based on a tier and a plan type.

It’s also useful when your table has headers that might change order, because you reference the header cells directly rather than hardcoding column numbers.

Alternatives and Advanced Variations

If you have Excel 365 or Excel 2021, you can use the XLOOKUP function, which can perform a two-way lookup with a simpler syntax:

=XLOOKUP(row_value, row_range, XLOOKUP(column_value, column_range, data_range))

This does the same thing but is easier to read. However, INDEX/MATCH still works in older versions and is more widely compatible.

For more complex scenarios, you can nest multiple INDEX/MATCH formulas to handle three-way lookups (e.g., by adding another MATCH for a second column header) or use array formulas to return multiple values at once. You can also combine INDEX/MATCH with other functions like SUMPRODUCT to sum all values that match two criteria, though that’s a different operation.

Frequently Asked Questions

Why does my INDEX/MATCH return #REF!?

This usually means the row or column number you gave to INDEX is larger than the data range’s dimensions. Check that your MATCH ranges are the same size as the data range, and that the MATCH result isn’t 0 (which happens when the lookup value isn’t found).

Can I use INDEX/MATCH for a left lookup?

Yes, that’s one of its main advantages. Unlike VLOOKUP, which can only return values to the right, INDEX/MATCH can return values to the left because you specify the exact result range. For example, to find a name based on an ID that’s in column B, you can use INDEX(A:A, MATCH(id, B:B, 0)).

What’s the difference between MATCH type 0 and 1?

Type 0 finds an exact match. Type 1 finds the largest value less than or equal to the lookup value, and requires the lookup array to be sorted ascending. Type -1 finds the smallest value greater than or equal to the lookup value, and requires descending sort. For most lookups, use 0.

Does INDEX/MATCH work with wildcards?

Yes, MATCH supports wildcards (* and ?) when match_type is 0. For example, MATCH("Nor*", A2:A10, 0) will match “North” or “Norway”. This can be handy for partial matches, but be careful with performance on large ranges.

Can I use INDEX/MATCH to return a whole row or column?

Yes. If you omit the column_num argument, INDEX returns the entire row. If you omit row_num, it returns the entire column. For example, INDEX(A1:C10, MATCH("North", A1:A10, 0), 0) returns all values in the row where “North” is found.

Why is my formula slow on a large table?

MATCH is generally fast, but if you have thousands of formulas each doing two MATCHes, it can add up. Consider using helper columns to pre-calculate row and column positions, or use Excel Tables and structured references to keep ranges small. Also, avoid full-column references like A:A when possible—limit the range to the actual data.

Related

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.