Index Match Across Two Sheets in Excel: A Practical Guide

Formulas & functions · Updated November 21, 2026

Yes. In Excel, INDEX and MATCH can look up a value on one worksheet and return a related value from another. If the lookup key is in A2 on the current sheet, and the other sheet has keys in column A and return values in column B, use =INDEX('Product List'!$B$2:$B$100,MATCH(A2,'Product List'!$A$2:$A$100,0)). The final 0 tells MATCH to find an exact match.

What INDEX MATCH across two sheets means

“Two sheets” simply means the lookup and result ranges can live on a worksheet different from the cell containing the formula. INDEX and MATCH do not need a special cross-sheet mode: a worksheet reference identifies where each range is. The formula can be entered on a third sheet, too, as long as its references point to the right places.

In the example above, the formula is on a worksheet containing a product code in A2. The worksheet named Product List has product codes in A2:A100 and product names in B2:B100. MATCH locates the row containing the code from the current sheet; INDEX returns the value in the corresponding position of the product-name range. If A2 contains P-104 and P-104 is on the sixth row of the lookup range, INDEX returns the value on the sixth row of the return range.

This approach is useful when one sheet holds a report, order list, or data-entry form and another holds a reference table. You can keep the source table separate instead of copying its values into the working sheet. It is also useful when the value to return is to the left of the lookup column, something a traditional VLOOKUP setup cannot do without rearranging columns.

How the formula works

The formula combines two functions, each with a distinct job. MATCH searches a one-dimensional range and returns a position, not the matched text itself. INDEX uses that position to retrieve a value from a corresponding range. Because both ranges start on row 2 and cover the same number of rows, their positions line up.

  • MATCH(A2,'Product List'!$A$2:$A$100,0) searches the product-code range for the value in A2.
  • The 0 requests an exact match, so the code must match rather than fall between two values.
  • INDEX('Product List'!$B$2:$B$100,...) returns the value in column B at the position MATCH found.

The dollar signs make the source ranges absolute. If you fill the formula down, the ranges stay fixed while A2 changes to A3, A4, and so on. The sheet name is enclosed in apostrophes because names containing spaces need them. Apostrophes are also acceptable around a simple sheet name, such as 'Data'!$A$2:$A$100. If a sheet name itself contains an apostrophe, Excel represents it with two apostrophes inside the quoted name.

Set up the sheets and enter the formula

Start with a clear source table. Put unique product codes in column A and the values to return in column B on the Product List sheet. On the working sheet, place a product code in A2 and put the formula in the cell where you want the product name. A practical setup is:

  1. Check the tab name and identify the source lookup and return columns.
  2. Enter the formula in the first result cell, changing the sheet name, ranges, and lookup-cell reference to fit your workbook.
  3. Press Enter. If the formula returns the expected value, fill it down for other lookup keys.
  4. Test a key that exists and one that does not, so you know how the formula behaves in both cases.

For example, suppose the working sheet has P-104 in A2, and the Product List table has P-104 on its seventh data row, with Desk Lamp in the matching row of column B. The formula returns Desk Lamp. The row position is counted within the specified ranges, not as the worksheet’s absolute row number; INDEX and MATCH stay aligned because both ranges begin on row 2.

In Microsoft 365 for Windows, you can enter the formula in the formula bar or directly in the cell. Excel for Mac uses the same formula syntax and sheet-reference approach. Ribbon labels and some shortcuts can differ on Mac, but no special menu command is required for this lookup. Excel for the web also supports INDEX and MATCH.

Use exact matches and handle missing values

For codes, names, invoice numbers, and most ordinary lookups, use 0 as MATCH’s third argument. Leaving this argument out is a common source of wrong results: MATCH defaults to an approximate-match mode that expects data sorted in a particular order. With exact-match mode, the source list does not need to be sorted.

If a lookup value is absent, MATCH returns #N/A, and that error passes through the formula. That can be useful while checking a workbook, but a report may be clearer with a message. With the same layout as the opening example, wrap the lookup in IFNA: =IFNA(INDEX('Product List'!$B$2:$B$100,MATCH(A2,'Product List'!$A$2:$A$100,0)),"Not found"). This returns the related value when there is a match and the text Not found when MATCH produces #N/A. IFNA handles that particular error; it does not hide unrelated errors such as a broken reference.

Do not use a blank-looking cell as proof that the lookup failed. The corresponding return cell might genuinely be empty. If you need to distinguish a missing key from a matched row with an empty result, test the key or show an explicit status separately.

Look up a value at the intersection of a row and column

INDEX MATCH can also find a value using both a row label and a column heading. This is helpful when the source sheet is a matrix, such as products down the rows and months across columns. In the following layout, Product List has product codes in A2:A100, month headings in B1:E1, and values in B2:E100. The current sheet has a product code in A2 and the desired month heading in F1. Use:

=INDEX('Product List'!$B$2:$E$100,MATCH(A2,'Product List'!$A$2:$A$100,0),MATCH(F1,'Product List'!$B$1:$E$1,0))

The first MATCH supplies the row position within the values area; the second supplies the column position. For instance, if A2 identifies the fourth product in the source range and F1 matches the third month heading, INDEX returns the value at the fourth row and third column of B2:E100. Both matches use exact-match mode. If either the product or heading is absent, the formula returns #N/A.

Common problems and how to diagnose them

If a formula gives #N/A when the key appears to be present, check whether the values are truly identical. A number stored as text is not always equivalent to the same number stored numerically; for example, a text code “0012” is different from the number 12. Leading or trailing spaces can also prevent a match. Compare the source and lookup values, standardize their data types, and remove unintended spaces where appropriate.

  • Wrong sheet or range: Verify the tab name and make sure the lookup and return ranges cover the intended records.
  • Ranges with different lengths: The lookup and return ranges should have matching row counts and start on corresponding rows.
  • Duplicate keys: MATCH returns the position of the first exact match it encounters. If a key occurs more than once, INDEX MATCH does not automatically return every matching record.
  • Copied formulas point somewhere unexpected: Use dollar signs on fixed source ranges, then check that the lookup cell changes as intended when filling down.
  • Unexpected result: Confirm the return column is the one associated with the lookup key, not an adjacent field with a similar heading.

For a large or frequently updated table, Excel Tables can make source ranges easier to maintain as rows are added. Structured references can be used in formulas, but their exact names depend on the table and column names in the workbook. With ordinary cell references, remember that a fixed range ending at row 100 will not automatically include a new record entered on row 101.

INDEX MATCH compared with XLOOKUP

For a single-condition lookup, XLOOKUP can be more direct because it takes a lookup range and a return range without nesting MATCH inside INDEX. With the same layout as the opening example, its form is =XLOOKUP(A2,'Product List'!$A$2:$A$100,'Product List'!$B$2:$B$100,"Not found"). It is available in Microsoft 365 and Excel 2021 or later, including Excel 2024. Older perpetual versions such as Excel 2019 do not include XLOOKUP, so INDEX MATCH remains a useful compatible option.

INDEX MATCH is still worth understanding when you inherit a workbook, need compatibility with older Excel versions, or want to see clearly how a position from one range selects a value in another. For exact lookups, both approaches can solve the basic cross-sheet task. Neither formula automatically resolves duplicate keys; each returns one result unless you build a separate method for multiple matches.

Frequently Asked Questions

Can INDEX MATCH look up data on another sheet?
Yes. Put the other sheet’s name and an exclamation mark before each source range, as in 'Product List'!$A$2:$A$100. The formula itself can be entered on any worksheet.

Do the two sheets need to have the same layout?
No. The lookup key and return value can be in different columns and the sheets can have different layouts. The lookup range and return range in a one-way INDEX MATCH must, however, contain corresponding rows.

Why does MATCH return #N/A when I can see the value?
Check for text-versus-number differences, extra spaces, a mismatched range, or a value that only looks the same because of formatting. Exact match requires the underlying values to match.

Does INDEX MATCH return every duplicate match?
No. MATCH finds the first matching position, so the formula returns the related value from that occurrence. Use a different approach if you need a list of all records sharing a key.

Can I use this formula in Excel for Mac?
Yes. INDEX, MATCH, and cross-sheet references work in Excel for Mac with the same formula structure. Menu locations and keyboard shortcuts can differ, but the lookup does not require a Windows-only feature.

Related

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.