INDEX MATCH Examples: Exact, Two-Way, and Flexible Lookups

With product names in A2:A6, prices in B2:B6, and the product to find in E2, use =INDEX($B$2:$B$6,MATCH(E2,$A$2:$A$6,0)). If E2 contains Desk lamp and that product’s price is 24.99, the formula returns 24.99. INDEX MATCH combines a value-returning function with a position-finding function, making it useful for exact, two-way, leftward, and approximate lookups in Excel.

What INDEX MATCH does

INDEX and MATCH solve two parts of a lookup. INDEX returns a value from a range at a specified position. MATCH finds the position of a lookup value within a range. Put together, MATCH identifies which row contains the item, and INDEX returns the corresponding value from another column.

The core pattern is INDEX(return range, MATCH(lookup value, lookup range, 0)). In this pattern, the zero tells MATCH to find an exact match. The lookup and return ranges should generally have the same number of rows and be aligned: the first item in the return range must correspond to the first item in the lookup range.

Unlike VLOOKUP, INDEX MATCH does not require the lookup column to be the first column in a table. It also does not rely on a fixed column number in the formula, so inserting a column between the lookup and result columns is less likely to break the lookup logic. It is a formula combination, not a separate Excel function named INDEX MATCH.

Basic exact-match example

Suppose A2:A6 contains product names and B2:B6 contains their prices. E2 contains the product name to search for. With those ranges and the example values above, this formula returns the price associated with the matching name:

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

The parts have distinct jobs:

MATCH(E2,$A$2:$A$6,0) searches for the value in E2 and returns its position within A2:A6.
INDEX($B$2:$B$6,...) returns the value at that position in B2:B6.
The final 0 requests an exact match, so the lookup does not depend on the list being sorted.

The dollar signs make the two table ranges absolute references. If you copy the formula down, those ranges stay fixed while E2 can change to E3, E4, and so on. Press F4 while editing a reference in Excel for Windows to cycle through reference styles; on Mac, the equivalent shortcut is typically Command+T, though keyboard behavior can depend on the Mac’s settings.

Look up a value to the left

INDEX MATCH can return a result from a column to the left of the lookup column. For example, suppose product IDs are in A2:A6, product names are in B2:B6, and E2 contains a product name. If Desk lamp has ID 1042, the formula returns 1042:

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

MATCH finds the product name’s position in column B. INDEX then uses that same position in column A. This is a common reason to choose INDEX MATCH over VLOOKUP: VLOOKUP’s standard arrangement looks for a value in the first column of its table and returns a value from a column to its right. INDEX MATCH allows the lookup and return ranges to be arranged independently.

Match on more than one condition

A lookup sometimes needs to identify a record by two fields, such as employee and department. Suppose employee names are in A2:A8, departments in B2:B8, and amounts in C2:C8. E2 contains the employee name and F2 the department. If the matching employee-department row has an amount of 7,200, this formula returns 7,200:

=INDEX($C$2:$C$8,MATCH(1,($A$2:$A$8=E2)*($B$2:$B$8=F2),0))

Each comparison produces a series of TRUE or FALSE results. Multiplying the comparisons converts rows where both conditions are true to 1; rows that fail either condition become 0. MATCH searches for 1 and returns the position of the first row meeting both conditions. In Microsoft 365 for Windows, this formula can be entered normally with Enter; it does not require the older Ctrl+Shift+Enter array-formula entry.

This pattern returns the first row that meets both conditions. If duplicate employee-and-department combinations exist, it will not automatically add their amounts or return every matching row. Check whether the key fields are unique, or use a summary or filtering approach if the task is to combine or list duplicates.

Build a two-way lookup

For a table organized by row labels and column headings, use one MATCH to find the row and another to find the column. Suppose product names are in A2:A5, quarter headings are in B1:E1, and the sales figures are in B2:E5. H2 contains a product name and H3 a quarter. If the selected product’s Q3 figure is 31, this formula returns 31:

=INDEX($B$2:$E$5,MATCH(H2,$A$2:$A$5,0),MATCH(H3,$B$1:$E$1,0))

INDEX receives a two-dimensional range, followed by a row number and a column number. The first MATCH identifies the product’s row; the second identifies the quarter’s column. Because both MATCH functions use 0, the product and heading must match exactly. This layout is useful for schedules, price matrices, and reports where users select both a category and a period.

Use approximate matching for thresholds

Approximate MATCH can find the position of the largest threshold that is less than or equal to a lookup value. This is useful for bracket tables, such as shipping bands or commission rates, provided the threshold values are sorted in ascending order. Suppose thresholds 0, 1,000, 5,000, and 10,000 are in A2:A5, with corresponding rates 2%, 4%, 8%, and 10% in B2:B5. With a sales value of 6,200 in E2, the formula returns 8%:

=INDEX($B$2:$B$5,MATCH(E2,$A$2:$A$5,1))

Here, the final 1 tells MATCH to use approximate matching for ascending-sorted values. The 6,200 falls between 5,000 and 10,000, so MATCH selects the 5,000 threshold and INDEX returns the rate beside it. If the thresholds are unsorted, the result may be wrong. Use 0 for ordinary exact lookups; do not omit the match type when an exact match is intended.

Handle missing matches and common errors

If the lookup value is absent from the lookup range, MATCH returns #N/A. You can display a more helpful message with IFNA. Suppose product names are in A2:A6, prices in B2:B6, and E2 contains the requested product. If E2 has no match, the formula displays No match:

=IFNA(INDEX($B$2:$B$6,MATCH(E2,$A$2:$A$6,0)),"No match")

IFNA catches #N/A specifically, while other errors remain visible for investigation. That distinction can help reveal an incorrectly sized range or a broken reference rather than concealing it. Before wrapping a formula, check these common causes of unexpected results:

Extra spaces or inconsistent text: A name with a trailing space is not necessarily identical to the same name without one. Clean the source data when practical.
Numbers stored as text: A text value such as “1042” may not match a numeric 1042. Check the underlying data type, not only how the cell looks.
Misaligned ranges: If the lookup range starts on row 2 but the return range starts on row 3, INDEX can return a value from the wrong record.
Duplicate keys: An exact MATCH returns the position of the first matching entry. Decide whether that is the intended record.
Wrong match type: Using 1 or -1 when you need an exact match can lead to misleading results, particularly if the data is not sorted as required.

For a quick check, temporarily put the MATCH expression in an empty cell and see whether it returns the expected position or #N/A. Then confirm that the corresponding position in the return range contains the value you expect.

INDEX MATCH compared with XLOOKUP

In current Excel, XLOOKUP can often express a simple lookup more directly. It takes a lookup value, a lookup array, and a return array, and exact matching is the default. XLOOKUP is available in Microsoft 365 and Excel 2021 or later, but not in some older Excel versions. INDEX and MATCH remain useful when a workbook must support older versions, when a two-way lookup needs separate row and column positions, or when the formula’s separate steps make its logic clearer.

Another practical difference is that XLOOKUP has a built-in argument for what to return when nothing matches. INDEX MATCH can handle this with IFNA, as shown above. Neither choice automatically solves duplicate-key ambiguity: both need a clearly defined rule if several rows could match. For a straightforward one-column lookup in a modern Excel version, XLOOKUP may be easier to read; for compatibility or more involved table layouts, INDEX MATCH remains a flexible pattern.

Frequently Asked Questions

Does INDEX MATCH require the data to be sorted?
Not for an exact lookup when MATCH uses 0. Sorting matters for approximate matching: match type 1 expects ascending values, while match type -1 expects descending values.

Why does INDEX MATCH return #N/A?
Usually, MATCH cannot find an exact value in its lookup range. Check for spaces, inconsistent data types, spelling differences, and whether the lookup value is present. Also confirm that the formula uses the intended lookup range.

Can INDEX MATCH return a value from another sheet?
Yes. Use a sheet-qualified reference for the lookup and return ranges, such as Sheet2 followed by an exclamation mark and the cell range. The lookup logic is unchanged; ensure both ranges refer to the intended rows on that sheet.

Does INDEX MATCH return the first duplicate or every match?
A standard MATCH finds the first matching position, so the combined formula returns the result from that row. It does not return all duplicates. If you need multiple results, use a filtering method such as FILTER, available in Microsoft 365 and Excel 2021 or later.

Should I use INDEX MATCH or VLOOKUP?
Use INDEX MATCH when you need to look left, want the lookup and result columns to be independent, or need a two-way lookup. VLOOKUP can be simpler for a basic table with the lookup column at the left. For newer Excel versions, XLOOKUP is another direct option.

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.