Excel INDEX MATCH: How to Build Flexible Lookup Formulas

Formulas & functions · Updated November 19, 2026

In Excel, combine INDEX and MATCH to find a value in one range and return the corresponding value from another: with product names in A2:A20, prices in B2:B20, and the name to find in E2, use =INDEX(B2:B20,MATCH(E2,A2:A20,0)). MATCH locates the matching row, and INDEX returns the value from that position in the price range.

What INDEX and MATCH do

INDEX and MATCH are separate functions that solve different parts of a lookup. MATCH searches a range and returns the relative position of a value; INDEX uses a position to retrieve a value from a range. Together, they can look up a value without requiring the lookup column to be the leftmost column of the table.

For example, if MATCH finds a product in the third cell of A2:A20, it returns 3. INDEX then returns the third cell of B2:B20. The result is a value, not a link to a record: if the source data changes, the formula recalculates to show the corresponding value.

The ranges must be arranged so the position from MATCH points to the right record in the INDEX range. For a vertical lookup, use ranges with the same number of rows and matching row order. If the two ranges start on different rows or have different lengths, the returned value may be wrong even if the formula itself is valid.

Build a basic exact-match lookup

Suppose a worksheet has product names in A2:A20 and their prices in B2:B20, with the requested product name entered in E2. This formula finds the name and returns its price:

Layout: product names are in A2:A20, prices are in B2:B20, and the lookup value is in E2.

=INDEX(B2:B20,MATCH(E2,A2:A20,0))

The final argument, 0, tells MATCH to look for an exact match. If E2 contains a name found in the list, the formula returns the price from the corresponding row. For instance, if the match is in the fourth position of A2:A20, INDEX returns the fourth value of B2:B20. Exact match is usually the right choice for names, product codes, employee IDs, and other identifiers.

In the formula, the INDEX range comes first: B2:B20 is where the answer comes from. MATCH then searches E2 in A2:A20 and supplies the position to INDEX. Keep the lookup value and the ranges in the formula tied to the intended data; selecting a header row in one range but not the other is a frequent source of off-by-one errors.

Enter and adapt the formula

In Excel for Microsoft 365 on Windows, select the cell where you want the result, type the formula, and press Enter. Replace the example ranges with the actual lookup and return ranges in your workbook. You can type the references or select the ranges with the mouse while editing the formula.

  • Use absolute references such as $A$2:$A$20 and $B$2:$B$20 if you will copy the formula and want those ranges to stay fixed.
  • Leave a lookup input such as E2 relative if copied formulas should use E3, E4, and so on.
  • Keep the MATCH range and INDEX range the same height for a vertical lookup, and make sure their first rows refer to corresponding records.
  • Use 0 as MATCH’s third argument for exact matching rather than relying on its default behavior.

For example, with the product names in A2:A20, prices in B2:B20, and the lookup input in E2, fixed ranges can be written as:

=INDEX($B$2:$B$20,MATCH(E2,$A$2:$A$20,0))

Excel for Mac uses the same INDEX and MATCH formula syntax. Function argument separators can depend on your regional settings: if Excel rejects commas, your installation may require semicolons instead. The examples here use commas.

Look up a value from a row and column

INDEX and MATCH can also identify a value at the intersection of a matching row and a matching column. This is useful for a table where each row is a product and each column is a location, month, or category. One MATCH finds the row position, and a second MATCH finds the column position.

Layout: product names are in A2:A5, the column headers are in B1:D1, the table values are in B2:D5, the product to find is in G2, and the header to find is in H2.

=INDEX(B2:D5,MATCH(G2,A2:A5,0),MATCH(H2,B1:D1,0))

The first MATCH searches the product list and supplies INDEX’s row number. The second searches the headers and supplies INDEX’s column number. If G2 contains the second listed product and H2 contains the third listed header, the formula returns the value in the second row and third column of B2:D5. Both MATCH functions use 0, so both lookups require exact matches.

Look left or match more than one condition

Unlike VLOOKUP, INDEX and MATCH do not require the lookup column to be left of the result column. To return a value from a column to the left, search the column containing the key and return from the separate column containing the answer.

Layout: employee names to return are in A2:A20, employee IDs to search are in B2:B20, and the ID to find is in E2.

=INDEX(A2:A20,MATCH(E2,B2:B20,0))

This returns the name associated with the ID, even though the name column is to the left of the ID column. It works because MATCH returns a relative position in B2:B20 and INDEX applies that position to A2:A20. The ranges still need to have corresponding records in the same order.

A lookup can also depend on two conditions, such as a person’s name and department. In Excel for Microsoft 365, array calculations let you combine two tests inside MATCH.

Layout: names are in A2:A100, departments are in B2:B100, salaries are in C2:C100, the name to find is in E2, and the department is in F2.

=INDEX(C2:C100,MATCH(1,(A2:A100=E2)*(B2:B100=F2),0))

Each comparison produces TRUE or FALSE values; multiplying the comparisons makes a row with both conditions true evaluate to 1. MATCH looks for that 1, and INDEX returns the salary from the same position. If multiple rows satisfy both conditions, MATCH returns the first one. If no row satisfies both, the formula returns #N/A. This array-style formula works in Microsoft 365; older Excel versions may require special array entry, so use a version-appropriate method if sharing the workbook with older users.

Understand match modes and duplicate results

MATCH has three match modes. The mode is the third argument in MATCH, and choosing it matters because the default is not exact matching:

  • 0: finds an exact match and does not require the lookup range to be sorted. This is the usual choice for identifiers and labels.
  • 1, or omitted: finds the largest value less than or equal to the lookup value. The lookup range must be sorted in ascending order for reliable results.
  • -1: finds the smallest value greater than or equal to the lookup value. The lookup range must be sorted in descending order.

Approximate modes are useful for ordered thresholds, such as assigning a band based on a score, but they are risky on an unsorted list. A formula can return a plausible-looking result rather than an obvious error if the data does not meet the sorting requirement. For a standard lookup, explicitly use 0.

When exact matching finds duplicates, MATCH returns the position of the first matching entry in the lookup range. It does not return every match or choose the newest record automatically. If the first result is not the desired one, check for duplicate keys or add another condition to distinguish the records.

Fix common errors and unexpected results

The most common error is #N/A, which means MATCH did not find the requested value under the chosen match mode. Check for misspellings, extra spaces, mismatched data types, or a lookup value that is absent from the range. A number stored as text may not match a numeric value, even when both look the same on screen. Also confirm that the formula uses the intended lookup range and exact-match mode.

With product names in A2:A20, prices in B2:B20, and the requested name in E2, you can show a readable message when an exact match is missing:

=IFNA(INDEX(B2:B20,MATCH(E2,A2:A20,0)),"Not found")

IFNA catches the #N/A result and substitutes the text Not found. It does not fix the underlying lookup, so use it to make a report easier to read, not to hide errors caused by a wrong range or an incorrect formula. If other errors need handling too, IFERROR can catch a broader range of error types, but that may also conceal problems worth investigating.

A wrong but non-error result often points to misaligned ranges, an unintended approximate match, or duplicates. Check that each position in the MATCH range corresponds to the same record in the INDEX range. If you copied the formula, inspect whether references shifted; absolute references can keep lookup ranges fixed. If names contain spaces or inconsistent text, clean the source values or verify the exact contents of the cells.

INDEX MATCH compared with XLOOKUP

In Excel for Microsoft 365, XLOOKUP can often express a simple exact lookup in one function. It has an explicit lookup range and return range, and it can return values to either side of the lookup range. XLOOKUP is available in Microsoft 365 and Excel 2021 or later, but it is not supported in some older Excel versions.

INDEX and MATCH remain useful when a workbook must work in older Excel releases, when a familiar formula is already in place, or when you need to combine row and column positions in a two-way lookup. Both methods can be powerful; neither automatically returns all duplicate matches. Choose the approach that fits the workbook’s compatibility needs and how clearly other users can maintain it.

Frequently Asked Questions

Is INDEX MATCH better than VLOOKUP?
It can be more flexible: the lookup column does not have to be the first column of the table, and inserting a column within a VLOOKUP table range is less likely to disrupt the lookup logic. VLOOKUP may still be simpler for a straightforward table and remains familiar to many users. The best option depends on compatibility and how the worksheet is structured.

Why does INDEX MATCH return #N/A?
MATCH cannot find an exact match in the selected range. Check the lookup value, range references, extra spaces, and whether one value is stored as text while the other is numeric. For ordinary exact lookups, confirm that the third MATCH argument is 0.

Can INDEX MATCH return multiple results?
The basic formula returns one result: the first matching record. To display all matches, use a method designed to return multiple rows, such as FILTER in Microsoft 365 or Excel 2021 and later, or use a suitable approach for the Excel version in use.

Does INDEX MATCH work in older Excel versions?
INDEX and MATCH have been available in many older Excel versions, which is one reason they are used in workbooks shared across different releases. The two-condition array example may behave differently in older versions, so check the recipient’s Excel version before relying on it.

Do I need to sort the lookup range?
Not when MATCH uses 0 for an exact match. Sorting is required for reliable approximate matching with 1 or -1. If the order of records matters for which duplicate is returned, remember that an exact MATCH returns the first occurrence in the range.

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.