Excel INDEX MATCH Examples and How to Use Them

In Excel, INDEX MATCH looks up a value by finding its position with MATCH and returning the value at that position with INDEX. For an exact match, use =INDEX(return_range,MATCH(lookup_value,lookup_range,0)); for example, it can find an employee’s salary from a name. The examples below show exact, two-way, left-side, and approximate lookups, along with ways to handle common errors.

What INDEX MATCH does

INDEX and MATCH are separate worksheet functions that become especially useful when nested together. MATCH searches a range and returns a position number; INDEX uses a position number to return a value from a range. Together, they separate the task of finding a row from the task of choosing what to return from that row.

The final argument in MATCH controls how Excel compares values. For ordinary lookups, use 0 to request an exact match. This is important because omitting the argument does not mean exact match: MATCH defaults to an approximate-match mode that expects sorted data. INDEX MATCH works in older Excel versions as well as Microsoft 365, so it is useful in workbooks that may be opened by people using different editions.

INDEX returns a value at a specified position in a range or array.
MATCH finds a value in a range and returns its relative position, such as 3 for the third item in the range.
0 in MATCH requests an exact match and is the right default for names, IDs, product codes, and similar keys.

Basic exact-match example

Suppose a worksheet has employee names in A2:A6, departments in B2:B6, salaries in C2:C6, and a name to search for in E2. If E2 contains Mina, this formula returns the salary associated with the matching name:

With names in A2:A6, salaries in C2:C6, and the lookup name in E2, the formula returns 72000 when Mina’s salary in C4 is 72000:

=INDEX(C2:C6,MATCH(E2,A2:A6,0))

MATCH searches A2:A6 for the value in E2. If Mina is the third item in that range, MATCH returns 3. INDEX then returns the third item in C2:C6. The two ranges must correspond row by row: the name in each position must describe the salary in the same position. Headers should usually be left outside both ranges.

This pattern is useful when the lookup value changes. Replace E2 with a cell containing another employee name, and the formula returns that person’s salary without changing the formula. You can also type a fixed value into the formula, but a cell reference is generally easier to maintain and reuse.

Two-way lookup by row and column

A two-way lookup finds an item from a row label and a column label. For example, a sales table might list products down the left and quarters across the top. One MATCH identifies the product row, while another identifies the quarter column; INDEX returns the value where they intersect.

Assume product names are in A2:A5, quarter headers are in B1:D1, and the sales figures are in B2:D5. Put a product name in G2 and a quarter label in H2. With Desk in G2, Q2 in H2, and 18 in the Desk/Q2 cell, this formula returns 18:

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

In this form, INDEX needs both a row number and a column number. The first MATCH searches the product labels and supplies the row number. The second searches the quarter headings and supplies the column number. Each MATCH uses 0 so Excel looks for the requested label exactly. The data range and label ranges must line up: B2:D5 contains the figures, A2:A5 identifies its rows, and B1:D1 identifies its columns.

Return a value to the left of the lookup column

One reason people choose INDEX MATCH is that the lookup column does not have to be to the left of the return column. In a table with employee IDs in A2:A5 and names in B2:B5, you can search for a name and return the ID to its left.

With IDs in A2:A5, names in B2:B5, and a name in D2, suppose D2 contains Omar and A4 contains E104. This formula returns E104:

=INDEX(A2:A5,MATCH(D2,B2:B5,0))

MATCH finds Omar’s position in the names range, and INDEX uses that same position in the IDs range. This also works when the return column is farther to the left or right, as long as both ranges cover matching rows in the same order. Unlike older VLOOKUP patterns, you do not need to rearrange the source table just to put the return column after the lookup column.

Approximate match for bands and thresholds

Exact matches are best for identifiers, but some tasks need a range or threshold. A rate table might list the minimum score for each grade. With approximate MATCH mode 1, Excel finds an exact value or the next-smallest value. The lookup column must be sorted in ascending order for this mode to return a reliable result.

Assume minimum scores are in B2:B6, grade labels are in C2:C6, and the score to grade is in E2. If B2:B6 contains 0, 60, 70, 80, and 90, C2:C6 contains F, D, C, B, and A, and E2 contains 82, this formula returns B:

=INDEX(C2:C6,MATCH(E2,B2:B6,1))

The result is B because 80 is the largest threshold that does not exceed 82. The thresholds must be numeric and sorted from smallest to largest; a misplaced value can produce a plausible but incorrect result. If the lookup value is below the first threshold, MATCH returns an error, so decide how that case should be handled. Do not use approximate mode for unsorted names or codes.

Handle missing values and common errors

If MATCH cannot find an exact value, it returns #N/A, and that error passes through the combined formula. That may be useful because it makes missing data visible. If a user-friendly message is preferable, wrap the lookup in IFNA.

With names in A2:A6, salaries in C2:C6, and a lookup name in E2, this formula displays Not found if there is no exact match; if Mina is present with a salary of 72000, it returns 72000 instead:

=IFNA(INDEX(C2:C6,MATCH(E2,A2:A6,0)),"Not found")

IFNA handles #N/A specifically. IFERROR can catch other error types too, but it may conceal a different problem in the formula or source data. When a result seems wrong, check these common causes:

Ranges do not align: The lookup and return ranges should have the same number of rows and correspond to the same records.
Extra spaces: A value that looks like Mina may contain a leading or trailing space. Compare the source and lookup values, or clean the data before relying on a match.
Different data types: A number stored as text may not match a numeric value as expected. Check whether IDs or numbers were imported as text.
Duplicate lookup values: MATCH returns the position of the first matching item. If names are not unique, use a unique ID or another field to identify the intended record.
Wrong match mode: For exact lookups, include 0. Approximate mode can return a result that looks valid even when the lookup key is absent.

INDEX MATCH compared with VLOOKUP and XLOOKUP

VLOOKUP is familiar and can be convenient when the lookup column is at the left edge of the table and the return column is to its right. Its column number is entered as a number, however, so inserting or rearranging columns can make a formula harder to maintain. INDEX MATCH uses separate ranges and can return values from either side of the lookup column.

XLOOKUP offers a more direct lookup formula and is available in Microsoft 365 and Excel 2021 or later. For a simple exact lookup, it can be easier to read because its arguments identify the lookup and return ranges directly. INDEX MATCH remains valuable when a workbook must support older Excel versions, when a user already understands the pattern, or when a familiar formula structure is required for compatibility. Availability depends on the Excel edition used to open the workbook, not just the edition used to create it.

For new workbooks used only in current Excel, XLOOKUP is often a straightforward alternative. For files shared with older installations, INDEX MATCH is a broadly compatible option. Whichever method you use, keep the lookup key consistent and test how the workbook behaves when a value is missing or duplicated.

Building and checking an INDEX MATCH formula

When creating a formula, identify the lookup key, its location, and the field to return before typing. Keeping those parts clear makes it easier to audit a formula later or diagnose a wrong result.

Choose a lookup value that identifies the record, preferably a unique ID rather than a person’s name if names can repeat.
Select the range containing that key, and confirm it covers the same records and row order as the return range.
Use MATCH with 0 for an exact match unless the task specifically requires a sorted threshold lookup.
Use INDEX on the range containing the answer, then test the formula with a key you can verify in the source data.
Try a missing key and a duplicate key so you know whether the result or error behavior is appropriate for the workbook.

Excel formulas in this article use commas between arguments, as requested for standard English-language syntax. Some installations use different list separators based on regional settings; if Excel rejects a formula, check the separator expected by that installation. The underlying lookup logic is unchanged.

Frequently Asked Questions

Is INDEX MATCH better than VLOOKUP?
It can be more flexible because the return range can be on either side of the lookup range, and the formula does not rely on a numbered return column. VLOOKUP may be simpler for a basic table where the lookup column is first and the answer is to its right.

Why does INDEX MATCH return #N/A?
Usually, MATCH cannot find an exact value in the lookup range. Check spelling, spaces, data types, the selected range, and the 0 match argument. If a missing value is expected, use IFNA to display a clear message.

Does MATCH return the worksheet row number?
No. MATCH returns a position relative to the range it searches. If the match is the third item in A2:A6, MATCH returns 3, even though the corresponding worksheet row is row 4. INDEX uses that relative position when its return range begins on the corresponding row.

Can INDEX MATCH return multiple results?
The standard pattern returns one value, and MATCH selects the first matching position. To list every matching record, a different approach is usually needed; FILTER is available in Microsoft 365 and Excel 2021 or later and can return multiple matching rows.

Is INDEX MATCH case-sensitive?
No. A standard MATCH lookup does not distinguish uppercase from lowercase. If case matters, the lookup needs a different formula approach; for ordinary names and labels, case-insensitive matching is generally expected.

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.