In Excel, INDEX and MATCH work together to find a value in one range and return the corresponding value from another. With product names in A2:A6, prices in B2:B6, and the product to find in E2, enter =INDEX(B2:B6,MATCH(E2,A2:A6,0)) to return the matching price; if E2 contains Notebook and Notebook costs 19.95, the result is 19.95.
What INDEX and MATCH Do
MATCH searches a range and reports the relative position of a value. INDEX uses a position to return the value at that location in a different range. In the example above, MATCH finds Notebook in the first position of A2:A6, and INDEX returns the first value from B2:B6. The two ranges need to line up: each product name should correspond to the price on the same worksheet row.
MATCH does not return the found item itself; it returns a number. If the item is in the fourth cell of the searched range, MATCH returns 4, even if the range begins at row 2. INDEX then uses 4 as a position within its own range. This relative-position behavior is why the ranges can begin on different worksheet rows, provided their entries correspond in the same order.
INDEX and MATCH have been available in Excel for many versions, so they can be useful when a workbook needs to work with older releases as well as Microsoft 365. They are also useful for understanding the steps behind a lookup: one function locates a row or column, and the other retrieves the result.
The Basic Exact-Match Formula
For a typical lookup, use the syntax INDEX(return_range,MATCH(lookup_value,lookup_range,0)). The final zero tells MATCH to look for an exact match. Include it rather than relying on MATCH’s default, which is an approximate-match mode and can give an unexpected result if the data is not sorted as required.
To build the formula in a worksheet:
- Put the values to search, such as product names, in one column.
- Put the corresponding results, such as prices, in another column on the same rows.
- Enter the item you want to find in a separate cell.
- In the result cell, use INDEX around MATCH, choosing the result range for INDEX and the search range for MATCH.
With product names in A2:A6, prices in B2:B6, and the lookup item in E2, the formula is =INDEX(B2:B6,MATCH(E2,A2:A6,0)). If E2 contains Marker and the Marker row has 2.8 in column B, the formula returns 2.8. Use matching range sizes and avoid including a header in only one of the two ranges; otherwise, the positions may no longer refer to corresponding records.
Using INDEX and MATCH for a Two-Way Lookup
You can find a value at the intersection of a row and a column, which is often called a two-way lookup. In this setup, INDEX needs a data block, a row number, and a column number. One MATCH finds the correct row, while another finds the correct column heading.
Suppose employee names are in A2:A4, quarter headings are in B1:D1, and the sales figures are in B2:D4. Put an employee name in E2 and a quarter heading, such as Q2, in F2. Use =INDEX(B2:D4,MATCH(E2,A2:A4,0),MATCH(F2,B1:D1,0)). If Mia is the first employee in the data block and Q2 is the second heading, the formula returns the value in the first row and second column of B2:D4. For example, if that cell contains 1350, the answer is 1350.
The row and column lookup ranges must match the data block’s dimensions and order: A2:A4 lines up with the rows of B2:D4, and B1:D1 lines up with its columns. If the requested employee or quarter is missing, one of the MATCH functions returns an error, so INDEX cannot return a result.
Choosing MATCH’s Match Type
The third MATCH argument controls how the search works. For most everyday lookups, use 0 for an exact match. Approximate modes can be useful for ordered bands such as commission rates or grade thresholds, but they depend on the data being sorted correctly.
- 0: Finds an exact match and does not require the lookup range to be sorted. This is the usual choice for names, IDs, product codes, or category labels.
- 1, or omitted: Finds the largest value that is less than or equal to the lookup value. The lookup range must be sorted in ascending order. Because omission defaults to this mode, leaving the argument out can cause errors in an unsorted list.
- -1: Finds the smallest value that is greater than or equal to the lookup value. The lookup range must be sorted in descending order.
For example, an ascending list of thresholds can support a bracket lookup with match type 1, but it is not interchangeable with an exact search. If the task is to find an employee ID exactly, use 0. A formula that appears to work on a sorted sample may return misleading positions after someone sorts or edits the source data.
Lookups to the Left and Other Advantages
A common reason people choose INDEX and MATCH is that the return range does not have to sit to the right of the lookup range. For example, if product codes are in B2:B10, product names are in A2:A10, and the code to find is in E2, use =INDEX(A2:A10,MATCH(E2,B2:B10,0)). This returns the name from column A even though the search happens in column B.
That flexibility is different from the traditional VLOOKUP arrangement, which searches in the first column of its table array and returns a value from a column to its right. INDEX and MATCH can also keep the search and return ranges separate, making the intent clear when columns are inserted or rearranged. The formula still depends on choosing ranges that refer to the intended fields; it does not automatically identify a column by its header unless you add another MATCH.
Common Errors and How to Fix Them
An error usually points to a mismatch between the lookup value, the source data, or the selected ranges. Check the data before adding error-handling logic, since hiding an error can conceal a real problem.
- #N/A: MATCH did not find a suitable value. Check spelling, extra spaces, number-versus-text differences, and whether the lookup value exists. For an exact lookup, confirm that the final MATCH argument is 0.
- Wrong result: Check that lookup and return ranges start and end on corresponding rows. Also check for duplicate values: an exact MATCH returns the position of the first matching item it encounters.
- #REF!: The INDEX reference may be invalid, or a referenced row or column may have been deleted. Review the ranges and the row or column numbers supplied to INDEX.
- #VALUE!: Check that arguments are valid and that a two-way formula has both its row and column positions in the right argument slots.
Numbers stored as text can look identical to numeric values but fail to match them. Likewise, a name can contain a trailing space that is difficult to notice. You can inspect source cells, standardize the data, or test a cleaned helper column. If you want a friendly message when a value is not found, with the lookup value in E2, names in A2:A6, and prices in B2:B6, use =IFERROR(INDEX(B2:B6,MATCH(E2,A2:A6,0)),"Not found"). This returns Not found for any error from the lookup, so use it when that broad error handling is appropriate; it can otherwise mask a broken range or another issue.
INDEX and MATCH Compared with XLOOKUP
In Microsoft 365, XLOOKUP can perform many of the same exact-match lookups with a shorter formula. With product names in A2:A6, prices in B2:B6, and the requested product in E2, use =XLOOKUP(E2,A2:A6,B2:B6,"Not found"). If E2 contains Notebook, it returns the corresponding price; if no match exists, it displays Not found. XLOOKUP is available in Microsoft 365 and Excel 2021, but not in some older Excel releases.
INDEX and MATCH remain practical when compatibility with older Excel versions matters, when a workbook already uses them, or when you want a formula that explicitly separates the position search from the returned value. XLOOKUP is often easier to read for a straightforward lookup and has a built-in not-found argument. Neither choice removes the need to verify the source data, define an appropriate match behavior, and handle duplicates according to the task.
Good Practices for Reliable Formulas
Clear ranges and consistent data make lookup formulas easier to audit. Consider these habits when building a worksheet other people will use:
- Use exact matching with 0 for identifiers and text labels unless approximate matching is deliberate.
- Keep the lookup and return ranges aligned, and make sure the source data has no unintended duplicate keys.
- Use absolute references such as
$A$2:$A$6if you will copy a formula and need its source ranges to stay fixed. A lookup cell such as E2 can remain relative if each copied formula should read a different input cell. - Use descriptive worksheet labels and keep headers outside the data ranges unless the formula is designed to include them.
- When the data grows frequently, consider converting it to an Excel Table so its ranges can expand as rows are added. Check the formula after changing the source layout.
For a keyboard workflow on Windows, press Enter to commit a formula. On Mac, Enter or Return can commit a formula depending on the keyboard and Excel settings. Formula separators may vary with regional settings; the examples here use commas as required by common English-language installations. If Excel rejects a formula, check the list separator configured for that installation.
Frequently Asked Questions
Is INDEX MATCH better than VLOOKUP?
It can be more flexible: INDEX and MATCH can return a value from a column to the left of the search column, while VLOOKUP’s traditional table-array setup searches its first column and returns to the right. For a simple lookup in current Excel, XLOOKUP may be easier to read.
Why does my INDEX MATCH formula return #N/A?
Usually, MATCH cannot find the requested value. Confirm the lookup value is present, use 0 for an exact match, and check for extra spaces, text-versus-number differences, or a misspelled entry.
Does MATCH return the worksheet row number?
No. MATCH returns a position within the range it searches. If the match is the third item in A2:A10, MATCH returns 3, not worksheet row 4. INDEX uses that relative position in its return range.
Can INDEX and MATCH return the first duplicate only?
With an exact MATCH, the formula returns the first occurrence it finds in the lookup range. If duplicate keys are present, decide whether the first record is acceptable or whether the source data needs a unique identifier.
Is XLOOKUP available in every version of Excel?
No. XLOOKUP is available in Microsoft 365 and Excel 2021 or later, but not in some older perpetual versions. INDEX and MATCH are supported in older Excel releases and are a common alternative when compatibility is important.
Related
- Java Code to Compare Two Excel Files with Apache POI
- Find Similar Names in Excel: Fuzzy Matching
- Numbers vs. Excel: Key Differences and Which to Choose
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Conditional Formatting: Use a Formula for RulesYes. In Excel, conditional formatting can use a formula that evaluates to TRUE or FALSE to decide which…
- IF Cell Color in Excel: Formulas and WorkaroundsNo—Excel’s IF function cannot test a cell’s fill color directly. Use the value or status that causes the…
- Excel LAMBDA Examples for Reusable Custom FunctionsExcel’s LAMBDA function lets you turn a formula into a reusable custom function. For example, a definition with…
- Sort a Filtered List in Excel Without Mixing Up RowsTo sort a filtered list in Excel, open the filter arrow in the column you want to sort,…
- Excel Greater Than and Less Than Formulas ExplainedIn Excel, use > to test whether a value is greater than another and < to test whether…
- Excel Cell Color by Hex: Apply, Match, and DisplayYes. In Excel for Microsoft 365, you can apply a cell fill that corresponds to a hex color,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.