In Excel, INDEX MATCH combines INDEX and MATCH to look up a value and return a related value from another range. For an exact match, if product codes are in A2:A6, prices are in B2:B6, and the code to find is in E2, enter =INDEX(B2:B6,MATCH(E2,A2:A6,0)); if E2 contains P-1047 and that code has a price of $18.50, the result is $18.50.
What INDEX MATCH Does
A lookup needs two pieces of information: the value to search for and the value to return. MATCH finds the position of the searched value within a range. INDEX uses a position to return the value at the corresponding place in a different range. Together, they can search a column and return information from a neighboring column.
For example, a product list might have codes in one column and prices in the next. MATCH locates a code such as P-1047 in the code list. If it is the third item in that range, MATCH returns 3. INDEX then returns the third value from the price range. The position is relative to the specified range, not necessarily the worksheet row number.
This approach has long been used as an alternative to VLOOKUP. It does not require the search column to be the leftmost column in the overall data, and inserting a column between the search and return columns is less likely to disrupt the lookup design. INDEX MATCH is still useful in current Excel, even though newer lookup functions can make many everyday lookups shorter.
Build a Basic Exact-Match Formula
Arrange the lookup values and results in corresponding ranges. In this example, A2:A6 contains product codes and B2:B6 contains their prices. Put the code to search for in E2. The ranges should have the same number of rows, and each code in column A should refer to the price on the same row in column B.
With product codes in A2:A6, prices in B2:B6, and the code to find in E2, enter =INDEX(B2:B6,MATCH(E2,A2:A6,0)) in the result cell. If E2 is P-1047 and the corresponding value in B3 is $18.50, the formula returns $18.50.
- Click the cell where the answer should appear.
- Type the formula, adjusting the ranges and lookup cell to match your sheet.
- Press Enter and check that the returned value belongs to the matching record.
- Try a code that is not in the list to see how the workbook handles missing values.
The final argument in MATCH is 0, which requests an exact match. That is generally the safest choice for IDs, names, product codes, and other values that must match exactly. If there are duplicate lookup values, MATCH returns the position of the first match it encounters, so the result will come from the first corresponding row.
Read the Formula from the Inside Out
It helps to understand the nested parts rather than treating the formula as a single trick. In =INDEX(B2:B6,MATCH(E2,A2:A6,0)), the inner MATCH searches A2:A6 for the value in E2. The 0 specifies exact matching. Suppose the value appears in the second cell of A2:A6: MATCH returns 2, not the worksheet row number 3.
INDEX then takes that relative position and applies it to B2:B6. Its second argument, 2, means return the second cell of that range. Since the ranges start on corresponding rows, the returned value comes from the same record as the matched code. This alignment is essential: if the lookup range starts at A2 but the return range starts at B3, the result can be offset and incorrect.
In this common one-column pattern, INDEX has an array followed by a row number. INDEX also supports a row and column position when the source is a two-dimensional table, which is useful for looking up an intersection rather than a value from a single return column.
Look Up a Value at a Row-and-Column Intersection
A two-way lookup finds a row using one label and a column using another. Imagine product names in A2:A5, month headings in B1:D1, and sales figures in B2:D5. Put the product name in F2 and the month heading in G1. One MATCH finds the product’s row position; the other finds the month’s column position.
With products in A2:A5, month headings in B1:D1, sales data in B2:D5, the requested product in F2, and the requested month in G1, enter =INDEX(B2:D5,MATCH(F2,A2:A5,0),MATCH(G1,B1:D1,0)). If Paper is the second listed product, April is the third heading, and the value at that intersection is 240, the formula returns 240.
Both MATCH functions use 0 because the row and column labels are expected to match exactly. INDEX receives the sales-data block, then the row position, then the column position. Keep the label ranges aligned with the data block: the first item in A2:A5 must describe the first data row in B2:D5, while the first heading in B1:D1 must describe the first data column.
Use Approximate Match for Sorted Thresholds
Exact matching is not the only option. MATCH can also find the nearest eligible value in an ordered list, which can be useful for grading bands, commission tiers, shipping brackets, or tax-rate tables. In this mode, the lookup values must be sorted in ascending order. MATCH with 1 returns the position of the largest value less than or equal to the lookup value.
With minimum thresholds in A2:A6 sorted as 0, 50, 60, 70, and 80; corresponding labels or rates in B2:B6; and a score in E2, enter =INDEX(B2:B6,MATCH(E2,A2:A6,1)). If E2 is 72, MATCH selects the threshold 70, so INDEX returns the value in B5. For instance, if B5 contains the label Pass, the result is Pass.
Do not use this approximate-match pattern on an unsorted list: it can return a misleading result rather than a clear warning. Also distinguish a threshold lookup from a nearest-value lookup. This formula selects the largest threshold that does not exceed the input; it does not find whichever number is mathematically closest. If no threshold is low enough for the input, the result may be an error, so include a suitable lower bound when the table design requires one.
Handle Missing Values and Other Common Problems
If MATCH cannot find an exact value, the combined formula usually returns #N/A. That often means the value is absent, but it can also signal inconsistent data: a number stored as text, an extra space, a different spelling, or a mismatched code format. Check both the lookup cell and the source list before changing the formula.
With product codes in A2:A6, prices in B2:B6, and the requested code in E2, enter =IFNA(INDEX(B2:B6,MATCH(E2,A2:A6,0)),"Not found") to show a message instead of #N/A. If E2 does not match any code, the result is Not found; if the match exists and the corresponding price is $18.50, the result remains $18.50.
IFNA handles the not-found error specifically. It is usually more informative than wrapping the entire lookup in IFERROR, which can also hide unrelated problems such as a mistaken reference. Other practical checks include:
- Confirm that the lookup and return ranges cover the same number of records.
- Check for leading or trailing spaces, especially in imported text data.
- Confirm that codes with leading zeroes are stored consistently; a numeric 123 and text 00123 are not the same value.
- Look for duplicate keys. MATCH returns the first match, so duplicates may need to be resolved or handled deliberately.
- Use absolute references such as
$A$2:$A$6if you will copy the formula to other cells and want the source ranges fixed.
INDEX MATCH Compared with XLOOKUP and VLOOKUP
For a simple lookup in Microsoft 365, XLOOKUP is often easier to read because it accepts the lookup range and return range directly. With product codes in A2:A6, prices in B2:B6, and the requested code in E2, =XLOOKUP(E2,A2:A6,B2:B6,"Not found") returns $18.50 when E2 is P-1047 and can provide a not-found result as part of the function. XLOOKUP is available in Microsoft 365 and Excel 2021 or later, but not in Excel 2019 or Excel 2016.
INDEX MATCH remains useful when a workbook must work in older Excel versions, when a reader already understands the pattern, or when a two-way lookup suits the layout. It also makes the lookup and return ranges explicit. VLOOKUP is familiar and can be convenient for a table whose search column is on the left, but its column-index argument can be less resilient if the table structure changes. For many new Microsoft 365 workbooks, choosing XLOOKUP for a straightforward lookup is reasonable; knowing INDEX MATCH helps you maintain existing files and understand more flexible lookup formulas.
Make Lookups Easier to Maintain
Good source data matters as much as the formula. Keep one clear header row, use a consistent data type for each key column, and avoid merged cells inside the data range. If the list will grow, consider converting it to an Excel table with Insert > Table. Structured references can make formulas easier to interpret and can expand as table rows are added, although the basic INDEX MATCH example above uses ordinary cell ranges.
When copying formulas, decide which references should move. Relative references such as A2:A6 adjust when copied. Absolute references such as $A$2:$A$6 stay fixed. On Windows, F4 cycles through reference styles while editing a formula; on Mac, the equivalent shortcut may depend on the keyboard and Excel settings, so use the function-key modifier if required. You can also add dollar signs directly.
Finally, test more than one record: a value near the top, one in the middle, one near the bottom, and a value that is absent. That catches common range-offset and missing-value mistakes before a lookup is relied on in a report. When the source list is large or shared, document what makes a key unique and how missing results should be treated.
Frequently Asked Questions
Is INDEX MATCH better than VLOOKUP?
It can be more flexible because the return range can sit to either side of the lookup range, and it avoids VLOOKUP’s column-number argument. It is not automatically better for every sheet; choose the formula that fits the workbook’s Excel versions, layout, and maintainability needs.
Why does INDEX MATCH return #N/A?
The lookup value may not exist exactly as entered, or values may differ in type, spelling, or spacing. Check the source data and ranges first. If a missing match is an expected outcome, use IFNA to display a clear message.
Can INDEX MATCH return a value to the left?
Yes. INDEX can return from any specified range, so the return column does not have to be to the right of the lookup column. Make sure both ranges cover corresponding rows.
Does INDEX MATCH return the last duplicate?
The standard exact-match pattern returns the first matching item in the lookup range. If duplicate values have different associated results, decide which record should count rather than assuming the formula will select the intended one.
Do I need to sort data for an exact match?
No. With MATCH’s third argument set to 0, the lookup range does not need to be sorted. Sorting is required for the approximate-match pattern using 1, and the list must be in ascending order.
Related
- Searchable Data Validation List in Excel: How to Set Up
- Sample Customer List Excel: Fields, Template, and Setup
- Sales Order Tracking Excel Template: Setup and Tips
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Calculate the Median in Excel: Formula and ExamplesTo calculate a median in Excel, use the MEDIAN function. If your numbers are in B2:B10, enter =MEDIAN(B2:B10);…
- Excel MATCH Lookup Array: How to Use ItThe MATCH function in Excel finds the position of a lookup value within a one-dimensional range or array,…
- Volume of a Right Cylinder: Formula, Steps and ExamplesThe volume of a right cylinder is V = πr²h: multiply pi by the radius squared, then by…
- Filter Duplicates in Excel: Keep One or Remove All?To remove duplicate entries in Excel for Microsoft 365 on Windows, select your data and choose Data >…
- Oracle String Functions Explained with ExamplesOracle string functions operate on character data in SQL and PL/SQL. For everyday work, use functions such as…
- PMT Function Syntax in Excel: Arguments and ExamplesIn Excel, the PMT function syntax is =PMT(rate, nper, pv, fv, type). The first three arguments are required;…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.