INDEX MATCH With Two MATCH Functions: Practical Examples

Yes: the standard two-way lookup uses INDEX with one MATCH to find a row and a second MATCH to find a column. INDEX returns the value where those positions meet, making this pattern useful when a table’s row label and column heading can both change.

What using MATCH twice means

In a two-way lookup, the data is arranged as a grid: labels such as product names run down the left side, and labels such as months or departments run across the top. The first MATCH searches the row-label range for the requested item. The second searches the header range for the requested column. INDEX uses the two resulting positions to return the cell at their intersection.

The general structure is INDEX(data_range, MATCH(row_value, row_labels, 0), MATCH(column_value, column_headers, 0)). The final 0 in each MATCH requests an exact match. This is generally the safest choice for labels, because exact-match searches do not require the lookup list to be sorted.

Each MATCH result is a position within its own lookup range, not a worksheet row or column number. For example, the second item in a row-label range returns 2, even if that item happens to be on worksheet row 17. That relative position is what lets INDEX work with a data range that starts elsewhere on the sheet.

Basic two-way lookup example

Suppose A2:A4 contains the row labels North, South, and West; B1:D1 contains the column headings Jan, Feb, and Mar; and B2:D4 contains the corresponding sales figures. Put the requested region in G2 and the requested month in G3. With that layout, use:

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

If G2 contains South and G3 contains Feb, the first MATCH returns 2 because South is the second label in A2:A4. The second MATCH also returns 2 because Feb is the second heading in B1:D1. INDEX therefore returns the value in the second row and second column of B2:D4, which is cell C3. If C3 contains 240, the formula returns 240.

The formula remains useful if the requested labels change. Change G2 to West and G3 to Mar, for example, and it returns the value where the West row and Mar column intersect. You do not need to rewrite the formula for a different row or month, as long as the labels are present in the specified ranges.

How to build the formula step by step

Building the parts separately can make the structure easier to understand and troubleshoot:

Identify the rectangular block of values that INDEX should return from. In the example, that is B2:D4.
Identify the labels beside the rows in that block. Use A2:A4 for the region names.
Identify the headings above the columns in the block. Use B1:D1 for the months.
Use MATCH with the row input and its labels, followed by MATCH with the column input and its headings. Use 0 for exact matches.
Check that the row labels and column headings are unique where a single result is expected.

Keep the ranges aligned: B2:D4 has three rows and three columns, so the row lookup range must describe those three rows and the header lookup range those three columns. The ranges need not start in the same worksheet row or column as the data, but their lengths and order must correspond to it.

Lookup with two row conditions and a column heading

Sometimes one row label is not enough. Imagine a table with Region in A2:A7, Product in B2:B7, and monthly values in C2:E7. If a product appears in more than one region, a MATCH on product alone could find the wrong record. In Microsoft 365 for Windows, the row MATCH can test both conditions, while the second MATCH still selects the month.

For this example, assume A2:A7 contains East, East, West, West, North, North; B2:B7 contains Widget, Cable, Widget, Cable, Widget, Cable; C1:E1 contains Jan, Feb, Mar; and C2:E7 contains the values. Put the requested region in H2, product in I2, and month in J2. The formula is:

=INDEX(C2:E7,MATCH(1,(A2:A7=H2)*(B2:B7=I2),0),MATCH(J2,C1:E1,0))

With East in H2, Widget in I2, and Feb in J2, the condition arrays multiply to 1 only on the row where both tests are true. The first MATCH locates that row, and the second locates Feb. If the East/Widget/Feb cell D2 contains 15, the result is 15. In Microsoft 365, this array calculation is handled directly; older Excel versions may require special array-formula entry, so this version-specific technique is not as compatible as a simple two-way lookup.

This pattern does not make duplicate records disappear. If the same region and product combination occurs more than once, MATCH finds the first qualifying row. To get an unambiguous result, make the conditions identify one row, or use a method designed to return multiple matching records.

Why exact match and range alignment matter

For text labels, use MATCH’s third argument as 0. If that argument is omitted, MATCH uses approximate-match behavior, which assumes data is sorted in a particular order and can return an unexpected position when it is not. Exact matching is also appropriate for ordinary numeric identifiers when the value must be equal, rather than merely near the requested number.

Check the order of the ranges as well as their sizes. If row labels are arranged East, West, North but the data rows are arranged North, East, West, the formula can return a valid-looking value from the wrong row. The lookup list must follow the same order as the corresponding rows in the data block. The same principle applies to the header range and data columns.

Do not include the header row or label column inside the INDEX data block unless that is deliberately part of the return area. For the basic example, B2:D4 contains only values; A2:A4 and B1:D1 are separate lookup ranges. Mixing those boundaries is a common cause of off-by-one results.

Common errors and practical fixes

#N/A: One of the requested labels was not found exactly. Check spelling, extra spaces, number-versus-text differences, and whether the lookup value is in the selected range. MATCH does not treat the number 12 and text value 12 as identical in every context.
#REF!: INDEX has been given a row or column position outside its data range. This often happens when a MATCH range contains more items than the corresponding rows or columns in the INDEX block.
A plausible but incorrect result: Look for misaligned ranges, duplicate labels, or approximate-match mode. A formula can calculate successfully while pointing to the wrong intersection.
Unexpected first result: MATCH returns the first exact occurrence it encounters. If a row label or pair of conditions is repeated, the formula will not automatically choose the most recent or highest-priority record.

To show a friendlier message when no match is found, wrap the lookup in IFNA. With the basic example’s same ranges and input cells, use:

=IFNA(INDEX(B2:D4,MATCH(G2,A2:A4,0),MATCH(G3,B1:D1,0)),"No match")

This returns No match when the lookup produces #N/A. It does not hide every possible formula problem: a range-reference error or other error can still appear, which is useful because it signals that the formula itself may need correction.

When this pattern is a good choice

INDEX with two MATCH functions is especially handy when the lookup row and column are selected by users, or when a report needs to retrieve an intersection from a changing table. It also avoids hard-coding a column number: if the location of a month heading changes but the heading remains in the lookup range, the second MATCH finds its new position.

It is not automatically the best answer for every lookup. A simple one-column lookup needs only one MATCH, and a table with several possible matches may need a filtering or aggregation approach. For a formula that should return all matching rows rather than one intersection, Microsoft 365 functions such as FILTER may be more appropriate. Choose based on whether the task asks for one value, one intersection, or a set of records.

INDEX MATCH MATCH compared with XLOOKUP

In Excel for Microsoft 365, nested XLOOKUP functions can express the same basic two-way lookup. With the earlier layout—row labels in A2:A4, headers in B1:D1, values in B2:D4, and inputs in G2 and G3—one option is:

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

The inner XLOOKUP selects the column for the requested heading; the outer one finds the requested row within that selected column. XLOOKUP is available in Microsoft 365 and Excel 2021 or later. INDEX with MATCH remains useful for compatibility with older Excel versions and for readers familiar with the established pattern. Both approaches depend on correctly aligned source ranges and do not resolve duplicate labels by themselves.

Frequently Asked Questions

What does the second MATCH do in INDEX MATCH MATCH?
The first MATCH usually finds a row position, while the second finds a column position. INDEX uses both positions to return the value at their intersection.

Should both MATCH functions use 0?
For an exact lookup of labels or identifiers, yes. The 0 requests an exact match and avoids the sorted-data assumptions associated with approximate matching.

Can I use two MATCH functions to search for two criteria?
Yes, but the standard two-way formula uses its two MATCH functions for a row and a column. To test two conditions for the row, combine the condition tests inside one MATCH, then use the other MATCH for the column heading, as shown above.

Why does the formula return #N/A?
At least one MATCH usually cannot find its requested value. Check that the value is present in the correct range, that text and numbers have compatible types, and that there are no accidental spaces or spelling differences.

Does INDEX MATCH MATCH work if the table is not sorted?
Yes, when both MATCH functions use exact-match mode, 0. The lookup ranges do not need to be sorted for these exact matches.

What happens when there are duplicate row labels?
MATCH returns the first exact occurrence in the lookup range. Add another identifying condition if needed, or use a formula designed to return multiple results when the task requires them.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.