Excel INDEX MATCH Lookup: Formula, Examples, and Tips

Formulas & functions · Updated November 20, 2026

In Microsoft Excel for Microsoft 365, INDEX and MATCH work together to look up a value: MATCH finds the position of a match, and INDEX returns the value at that position. For example, if product names are in A2:A6, prices are in B2:B6, and the product to find is in D2, use =INDEX(B2:B6,MATCH(D2,A2:A6,0)); if D2 contains Notebook and that row’s price is 3.50, the result is 3.50.

What INDEX MATCH means in Excel

INDEX MATCH is a combination of two separate worksheet functions, not a single Excel function. It is used to retrieve a value from a list or table by matching a lookup item to a corresponding position. MATCH identifies the position of the requested item in a range; INDEX uses that position to return a value from another range.

The combination is useful when a table’s lookup column is not the first column, when you want a formula that can survive inserted columns, or when you need to look up a value at the intersection of a row and a column. It is also widely used in workbooks that need to support Excel versions without XLOOKUP.

Unlike a manually selected filter or Find command, an INDEX MATCH formula recalculates when its input changes. You can therefore place the lookup item in an input cell and reuse the result for different products, employees, dates, or other records.

How the two functions work together

MATCH searches a one-dimensional range and returns a position number, not the matching cell’s value. If the item is the third entry in the range, MATCH returns 3. INDEX then uses that position to return the third item from a corresponding return range. The position is relative to the start of each range, so the lookup and return ranges should line up row by row.

In the opening example, MATCH searches A2:A6 for the content of D2. If Notebook is in A4, it returns 3 because A4 is the third cell within A2:A6. INDEX then returns the third value from B2:B6, which is B4. The two ranges can be in different columns; what matters is that their entries correspond in the same order.

The final argument in MATCH controls the match type. Use 0 for an exact match, which is the appropriate choice for most names, codes, and product IDs. Leaving this argument out does not mean exact match: MATCH defaults to approximate matching, which assumes the lookup range is sorted. That default is a frequent source of unexpected results.

Build an exact-match lookup step by step

  1. Identify the lookup range: the cells that contain the items Excel should search.
  2. Identify the return range: the cells containing the answer, aligned in the same row order.
  3. Choose an input cell for the value to find, such as D2.
  4. Enter INDEX around MATCH, and use 0 as MATCH’s match type.
  5. Change the value in the input cell to confirm that the formula returns the corresponding record.

For this example, assume product names are in A2:A6, corresponding prices are in B2:B6, and the requested product is entered in D2. The formula is =INDEX(B2:B6,MATCH(D2,A2:A6,0)). If the rows contain Pen at A2, Folder at A3, Notebook at A4, Ruler at A5, and Tape at A6, with prices 1.25, 2.00, 3.50, 0.90, and 1.10 respectively, entering Notebook in D2 returns 3.50.

To enter the formula, select the result cell, type it in the formula bar or directly in the cell, and press Enter. On Windows, Ctrl+Enter fills the selected cell with the current entry; on Mac, the equivalent is Control+Return. For a formula in just one cell, ordinary Enter is simplest. If you copy the formula down a column, consider whether the lookup and return ranges need absolute references such as $A$2:$A$6 so they do not shift.

Use INDEX MATCH for a two-way lookup

A two-way lookup finds a value by both a row label and a column label, such as an employee’s sales for a particular quarter. INDEX can return a value from a rectangular table when given a row position and a column position. Use one MATCH to locate the row and a second MATCH to locate the column.

Assume employee names are in A2:A5, quarter headings are in B1:D1, and sales data is in B2:D5. Put the employee to find in F2 and the quarter heading in G2. Use =INDEX(B2:D5,MATCH(F2,A2:A5,0),MATCH(G2,B1:D1,0)). If F2 contains Lee and G2 contains Q2, the formula returns the value at the intersection of Lee’s row and the Q2 column. It returns the actual amount stored at that intersection; the answer depends on the table’s data.

For this formula to work, the row MATCH must search labels that correspond to the rows of the data block, while the column MATCH must search headings that correspond to its columns. Both MATCH functions use 0 for exact matching. A label that differs by an extra space or a different spelling will not be treated as the same label.

Approximate matches and sorted thresholds

Approximate matching is useful for assigning a category from a sorted set of thresholds, such as a shipping rate, discount tier, or grading band. It should not be substituted casually for an exact match. With match type 1, MATCH finds the largest value less than or equal to the lookup value and the lookup range must be sorted in ascending order. With match type -1, it finds the smallest value greater than or equal to the lookup value and the range must be sorted in descending order.

Suppose minimum score thresholds are in A2:A5 in ascending order, with labels in B2:B5: 0 is Needs work, 60 is Pass, 75 is Good, and 90 is Excellent. The score to classify is in D2. Use =INDEX(B2:B5,MATCH(D2,A2:A5,1)). If D2 is 82, MATCH selects the threshold 75, so INDEX returns Good. A score below the first threshold returns #N/A, and a wrongly sorted threshold list can produce a misleading classification. For labels, keep the threshold values sorted and check how values at the boundaries should be categorized.

Look up records using more than one condition

Sometimes one field is not unique: several rows may contain the same surname, item name, or date. In Microsoft 365, you can combine conditions inside MATCH so it searches for a row where all specified conditions are true. For example, assume employee names are in A2:A10, departments are in B2:B10, and the value to return is in C2:C10. Put a name in H2 and a department in I2, then use =INDEX(C2:C10,MATCH(1,(A2:A10=H2)*(B2:B10=I2),0)).

The two comparisons produce arrays of TRUE or FALSE values; multiplying them makes a row qualify when both conditions are true. MATCH looks for 1, and INDEX returns the corresponding value from column C. If more than one row satisfies both conditions, this returns the first matching row. If no row matches, it returns #N/A. In older Excel releases, array formulas can behave differently and may require Ctrl+Shift+Enter; Microsoft 365 handles this formula without that legacy entry step.

Why choose INDEX MATCH instead of VLOOKUP or XLOOKUP?

VLOOKUP searches for a value in the leftmost column of its table and returns a value from a column to its right. INDEX MATCH does not have that leftmost-column restriction: its lookup and return ranges can be on either side of one another. This is useful when a workbook’s layout cannot be rearranged. INDEX MATCH also uses separate ranges, so inserting a column within a larger table is less likely to change which return column the formula refers to than a VLOOKUP formula based on a fixed column number.

XLOOKUP is often easier to read because it combines lookup and return ranges in one function and uses exact match by default. XLOOKUP is available in Microsoft 365 and Excel 2021 or later, while INDEX and MATCH also work in many older Excel versions. If a workbook is shared with people using older releases, check compatibility before replacing INDEX MATCH with a newer function. The best choice depends on the oldest Excel version the workbook needs to support and how clearly other users can maintain the formulas.

INDEX MATCH is not automatically faster or better in every workbook. For small lists, all three approaches may be adequate. In large workbooks, performance depends on the workbook’s overall formulas, calculation settings, and data size. Use bounded ranges or Excel Tables rather than unnecessarily searching entire columns in many complex formulas, and test changes in a copy if calculation time matters.

Common errors and how to fix them

  • #N/A: MATCH did not find an exact match, or the approximate-match lookup value falls outside the usable thresholds. Check spelling, spaces, data types, and the match-type argument.
  • Wrong result: Confirm that 0 is present for an exact match. If match type is 1 or -1, verify the required sort order and understand the threshold behavior.
  • Numbers that look alike do not match: One cell may contain a number stored as text while the other contains a numeric value. Standardize the data type rather than relying on visual appearance alone.
  • Extra spaces: Leading or trailing spaces can make text values different. Clean the source data where possible; TRIM can help with ordinary spaces, but it does not remove every nonprinting character.
  • #REF!: Check that INDEX’s requested position is within the selected return range and that referenced cells or columns have not been deleted.

If you want a friendlier message when an exact lookup has no result, you can wrap the formula with IFNA. Assume product names are in A2:A6, prices in B2:B6, and the search value in D2. Use =IFNA(INDEX(B2:B6,MATCH(D2,A2:A6,0)),"Not found"). It returns the price when there is a match and the text Not found when MATCH returns #N/A. IFNA only handles #N/A; it does not conceal other kinds of formula errors.

Make lookup formulas easier to maintain

For a lookup that will grow over time, consider converting the source range into an Excel Table: select a cell in the data and choose Insert > Table. Tables expand when rows are added and provide named columns that can make formulas easier to understand. Keep lookup keys consistent and unique when the task expects one result; duplicate keys make MATCH return the first occurrence, which may not be the record you intended.

Use clear input cells and, where practical, data validation to reduce mistyped search values. Avoid merging cells in data ranges, keep one record per row, and use a single header row. These are not requirements of INDEX MATCH itself, but they make the underlying data more reliable and formulas simpler to audit. When troubleshooting, temporarily evaluate MATCH by itself in an empty cell using the same lookup value and lookup range. If it returns a position, compare that position with the corresponding return-range row; if it returns #N/A, investigate the lookup data first.

Frequently Asked Questions

Is INDEX MATCH one function in Excel?
No. INDEX and MATCH are separate functions combined in one formula. MATCH finds a position, and INDEX returns the corresponding value.

Should I use 0 in MATCH?
Use 0 when you need an exact match, which is the normal choice for identifiers, names, and product codes. The other match types are approximate and require the lookup range to be sorted in the specified order.

Can INDEX MATCH look to the left?
Yes. The lookup range and return range are separate, so the return column can be to the left or right of the lookup column. Make sure the two ranges align in the same row order.

Why does my formula return #N/A even though the value appears in the list?
Check for hidden spaces, spelling differences, or a number stored as text in one range but as a number in the other. Also confirm that the formula uses the intended lookup range and exact-match argument 0.

Does INDEX MATCH work in older Excel versions?
INDEX and MATCH are available in many older Excel versions, which is one reason the combination remains common. Newer functions such as XLOOKUP are not available in every older release, so verify the versions used by everyone who needs the workbook.

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.