Excel INDEX MATCH Formula: How to Use It in Microsoft Excel

Formulas & functions · Updated November 20, 2026

In Microsoft Excel, combine INDEX and MATCH to find a value in one range and return the value from the same position in another: =INDEX(B2:B6,MATCH(E2,A2:A6,0)). With product codes in A2:A6, prices in B2:B6, and the code SKU-103 in E2, this formula returns 7.25; the final 0 tells MATCH to look for an exact match.

What INDEX and MATCH Do

INDEX and MATCH are separate worksheet functions that work together. INDEX returns a value from a range at a specified position. MATCH searches a range and returns the position of a matching item, rather than returning the item itself. In the example above, MATCH finds SKU-103 in the third cell of A2:A6 and returns the position 3. INDEX then returns the third cell in B2:B6, which contains 7.25.

That position-based approach is useful because the lookup range and result range do not have to be in a particular left-to-right order. You can search a column and return a value from a column to its left, or use row and column matches to locate a value inside a table. The ranges must still line up logically: each item in the lookup range should correspond to the item in the same position in the result range.

The Basic INDEX MATCH Formula

For a lookup down a single column, the general pattern is INDEX(return_range,MATCH(lookup_value,lookup_range,0)). In the formula, return_range is where the answer should come from, lookup_value is the item you want to find, and lookup_range contains the items to search. The 0 argument requests an exact match. It is good practice to include it rather than rely on MATCH’s default behavior.

For the example data, A2:A6 contains SKU-101, SKU-102, SKU-103, SKU-104, and SKU-105; B2:B6 contains 12, 18.5, 7.25, 24, and 9.75; E2 contains SKU-103. The formula =INDEX(B2:B6,MATCH(E2,A2:A6,0)) returns 7.25. If E2 instead contains SKU-104, it returns 24. The formula can be copied to other lookup rows if its ranges are adjusted or correctly locked with dollar signs.

The order of the ranges matters. MATCH searches A2:A6, while INDEX returns from B2:B6. If you accidentally use B2:B6 as the MATCH range, Excel will search prices for a product code and usually return #N/A. Likewise, if one range has a different number of rows from the other, positions may no longer refer to corresponding records.

Build the Formula Step by Step

To create the lookup in a worksheet, first identify the column containing the item to search and the column containing the answer. Then choose a cell for the lookup value, such as E2. Start with MATCH, confirm that it finds the item’s position, and wrap it in INDEX to retrieve the corresponding result.

  1. Enter or select the value to look up, such as SKU-103 in E2.
  2. Use MATCH with the lookup value, the lookup range, and 0 for exact matching.
  3. Use INDEX around that MATCH expression, specifying the range that contains the answer.
  4. Press Enter and check that the result belongs to the matching record.

A useful way to troubleshoot or learn the formula is to temporarily calculate MATCH by itself. With SKU-103 in E2 and product codes in A2:A6, =MATCH(E2,A2:A6,0) returns 3. This makes clear that MATCH returns a position, not a price or product code. Once that result is correct, put the MATCH expression inside INDEX.

Look Up a Value in a Two-Way Table

INDEX MATCH can locate an intersection in a table by using one MATCH for the row and another for the column. Suppose employee names are in A3:A5, month headings are in B2:D2, and the values for each employee and month are in B3:D5. If F2 contains Blair and F3 contains Feb, the formula below returns Blair’s February value. Assume the table has Alex’s values 120, 135, and 128; Blair’s 90, 110, and 105; and Casey’s 140, 132, and 150.

With names in A3:A5, month headings in B2:D2, data in B3:D5, employee in F2, and month in F3, =INDEX(B3:D5,MATCH(F2,A3:A5,0),MATCH(F3,B2:D2,0)) returns 110. The first MATCH supplies the row number within the data range; the second supplies its column number. This is handy for reports where a user chooses both a person and a period, and the formula returns the value at their intersection.

Exact and Approximate Matching

The third argument of MATCH controls how it searches. For most ordinary lookups, use 0. Exact matching works with text, numbers, and dates, and does not require the lookup data to be sorted. MATCH is not case-sensitive in a standard exact match, so names such as Sam and SAM are treated as equivalent for this purpose.

  • 0: Finds an exact match. The lookup range does not need to be sorted. This is usually the safest choice for codes, names, and identifiers.
  • 1, or omitted: Finds the largest value less than or equal to the lookup value. The lookup range must be sorted in ascending order. This can be useful for threshold tables such as graduated rates or quantity bands.
  • -1: Finds the smallest value greater than or equal to the lookup value. The lookup range must be sorted in descending order.

Approximate matching is not a looser version of exact matching that can safely be used on any list. If the range is unsorted, the result may be wrong or unexpected. For a price-band example, a sorted ascending list of minimum quantities can identify the applicable band, but the business rule and boundary values should be checked carefully before relying on the result.

Return Values to the Left and Use Wildcards

Unlike VLOOKUP, INDEX MATCH does not require the lookup column to be the first column in a rectangular table. For example, if prices are in A2:A6 and product codes are in B2:B6, you can search the codes and return the price from the column on their left. With prices in A2:A6, codes in B2:B6, and the desired code in E2, =INDEX(A2:A6,MATCH(E2,B2:B6,0)) returns the price paired with that code. The ranges must be the same height and arranged in corresponding row order.

With an exact match type of 0, MATCH also supports wildcard searches in text: an asterisk represents any number of characters, and a question mark represents one character. For instance, a search term can be combined with asterisks to find a cell containing that text anywhere within it. If the lookup range contains several matching entries, MATCH returns the position of the first one. A tilde can be used to search for a literal wildcard character rather than treating it as a wildcard.

Common Errors and How to Fix Them

The most common result problem is #N/A, which means MATCH did not find a match under the selected conditions. Check spelling, extra spaces, the selected range, and whether one value is stored as text while its apparent equivalent is stored as a number. For instance, a text code “0012” is not necessarily the same value as the number 12, and formatting a number to display leading zeroes does not turn it into text.

Other checks can help narrow down the cause:

  • Confirm that the MATCH range contains the lookup value and that the final argument is 0 for an exact search.
  • Make sure the INDEX and MATCH ranges cover corresponding rows or columns and have compatible dimensions.
  • Look for hidden leading or trailing spaces in imported text. Cleaning source data is often better than adding complicated workarounds to every lookup.
  • Check for #REF!, which can occur if a referenced range or row/column position is invalid, and verify that the INDEX range has not been changed.
  • For approximate matching, verify the required sort order and confirm that the lookup value falls within the table’s intended boundaries.

If a missing match is an expected possibility, IFNA can display a clearer message. With product codes in A2:A6, prices in B2:B6, and a lookup value in E2, =IFNA(INDEX(B2:B6,MATCH(E2,A2:A6,0)),"Not found") returns the matching price or the text Not found if MATCH returns #N/A. IFNA handles #N/A specifically; it does not conceal every other kind of formula error, which can make it preferable to a broad error-handling function when the goal is only to handle missing matches.

INDEX MATCH Compared with VLOOKUP and XLOOKUP

VLOOKUP is familiar and can be quick to write, but it searches in the first column of its table range and returns a result from a column to the right. INDEX MATCH can search one range and return from another range on either side, and it does not depend on a numeric column index that may need changing when columns are inserted. These characteristics can make it easier to maintain in worksheets whose layouts change.

XLOOKUP offers a more direct lookup pattern and can return values from either side of the search range. It is available in Microsoft 365 and Excel 2021 or later, but not in some older Excel editions. INDEX MATCH remains useful for compatibility with older workbooks and for readers learning how position-based lookups work. For a new formula in an environment that supports XLOOKUP, compare the workbook’s version requirements and team conventions before choosing; there is no need to replace a working INDEX MATCH formula solely because another function exists.

Frequently Asked Questions

Does INDEX MATCH require the lookup column to be first?
No. MATCH searches the range you specify, and INDEX returns from a separate range. The lookup range can be to the right or left of the result range, provided the positions correspond.

Why does MATCH return a number instead of the value I want?
MATCH returns the relative position of the match within its search range. Put MATCH inside INDEX and provide the range that contains the value you want returned.

Is INDEX MATCH better than VLOOKUP?
It is more flexible when the result column is left of the lookup column and can be less dependent on fixed column numbers. VLOOKUP may still be adequate for simple layouts; the better choice depends on the workbook’s structure and Excel version needs.

Can INDEX MATCH return more than one result?
The standard formula described here returns the value at one matched position. For multiple matches, a different approach is needed; FILTER is available in Microsoft 365 and Excel 2021 or later and can return all rows meeting a condition.

Is the lookup case-sensitive?
No. A normal MATCH exact search is not case-sensitive. If capitalization must distinguish records, the lookup needs a case-sensitive method rather than the basic INDEX MATCH pattern.

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.