To look up a value with INDEX MATCH in Excel, use MATCH to find the position of a key and INDEX to return the value at that position. For an exact lookup, the pattern is INDEX(return_range,MATCH(lookup_value,lookup_range,0)); the final 0 tells MATCH to find an exact match.
What INDEX MATCH does
INDEX and MATCH are separate Excel functions that solve a lookup together. MATCH searches a range and returns a position number, not the value found there. INDEX takes a range and a position number, then returns the value at that position. Since both ranges can be selected independently, the lookup column does not have to be the first column in the table.
This makes the pair useful for finding a price from a product code, a department from an employee name, or a result at the intersection of a row and column. The key idea is that MATCH supplies INDEX with a location. If the fourth item in the lookup range matches, INDEX returns the fourth item in the return range.
INDEX MATCH is also useful when a worksheet may change over time. In a traditional lookup, inserting or rearranging columns can make a fixed column number return the wrong field. With INDEX MATCH, you specify the return range directly, so the formula is easier to read and less dependent on the table’s column order.
Build a basic exact-match formula
Suppose names are in A2:A6, their departments are in B2:B6, and E2 contains the name Mira. The rows are Ava—Finance, Ben—Support, Mira—Sales, Omar—Operations, and Zoe—Marketing. To return Mira’s department, enter this formula in another cell:
With names in A2:A6, departments in B2:B6, and the lookup name Mira in E2, the formula returns Sales: =INDEX(B2:B6,MATCH(E2,A2:A6,0))
Read the formula from the inside out. MATCH looks for E2 in A2:A6. Its third argument, 0, requests an exact match, and the match is in the third position. INDEX then returns the third item in B2:B6, which is Sales. The two ranges must line up: the first name must correspond to the first department, the second name to the second department, and so on.
A reliable way to enter the formula is:
- Choose the cell where the answer should appear.
- Type INDEX and select the range containing the answer you want.
- Inside INDEX, enter MATCH, then select the cell holding the lookup value and the range to search.
- Use 0 as MATCH’s third argument for an exact match, close both functions, and press Enter.
In Microsoft 365 for Windows, Enter confirms the formula. The same formula structure works in Excel for Mac and Excel for the web; ribbon layout and some keyboard shortcuts differ, but the function arguments do not. Excel installations configured for a locale that uses semicolons as formula separators may display or require semicolons instead of commas.
Look up values in either direction
Because the lookup range and return range are independent, INDEX MATCH can return a value to the left of the lookup column. This is a common reason to choose it over VLOOKUP, which searches its first table column and returns a value to its right.
For example, suppose employee names are in A2:A6, employee IDs are in B2:B6, and E2 contains ID 104. If Mira is on the row with that ID, the formula below returns Mira even though the lookup column is to the right of the result column.
With names in A2:A6, IDs in B2:B6, and 104 in E2, the formula returns Mira: =INDEX(A2:A6,MATCH(E2,B2:B6,0))
This flexibility does not mean the two ranges can be mismatched in size or order. Keep the same row alignment in both ranges. If one range starts on row 2 and the other starts on row 3, MATCH’s position will point to the wrong record even if the formula has no syntax error.
Return a value from a two-way table
INDEX can use both a row number and a column number. That allows a two-way lookup, where one MATCH finds a row label and another MATCH finds a column label. For example, imagine a table with products Cedar, Maple, and Pine in A2:A4; month headings Jan, Feb, and Mar in B1:D1; and sales figures in B2:D4. Assume Maple’s February figure is 18. Put Maple in F2 and Feb in F3.
With product labels in A2:A4, month headings in B1:D1, figures in B2:D4, and Maple and Feb in F2 and F3, respectively, the formula returns 18: =INDEX(B2:D4,MATCH(F2,A2:A4,0),MATCH(F3,B1:D1,0))
The first MATCH finds the product’s row within A2:A4. The second finds the month’s column within B1:D1. INDEX uses those two positions to select the corresponding cell in B2:D4. This pattern is especially useful for cross-tab reports, such as choosing a region and quarter to retrieve a total. The heading ranges should cover the same columns as the data range, and the row-label range should cover the same rows.
Understand approximate matches
Exact matching is usually the safest choice for names, IDs, and product codes. Approximate matching has a different purpose: it finds a threshold or nearest qualifying value in an ordered list. MATCH with a match type of 1 finds the largest value less than or equal to the lookup value, provided the lookup range is sorted in ascending order. A match type of -1 finds the smallest value greater than or equal to the lookup value, with the range sorted in descending order. A match type of 0 requests an exact match and does not require sorting.
For a grading example, suppose minimum score thresholds 0, 60, 70, and 90 are in A2:A5, and their labels F, D, C, and A are in B2:B5. The thresholds are ascending. If E2 contains 84, the appropriate label is C because 70 is the greatest threshold no higher than 84.
With ascending thresholds in A2:A5, grade labels in B2:B5, and a score of 84 in E2, the formula returns C: =INDEX(B2:B5,MATCH(E2,A2:A5,1))
Do not use approximate matching on an unsorted threshold list and expect a dependable result. Also note that approximate matching assigns values according to the breakpoints you provide: a score below the first threshold may not have a suitable match. Check the threshold design and boundary cases before using the result in a report.
Match on more than one condition
A lookup may need to identify a record by two fields, such as both a sales representative and a region. In Microsoft 365, one option is to multiply the two logical tests inside MATCH. Excel treats TRUE and FALSE results as 1 and 0 in this calculation, so a row that meets both conditions produces 1. MATCH then locates that row.
Suppose representative names are in A2:A6, regions in B2:B6, and units sold in C2:C6. Let E2 contain Riley and F2 contain West. If the Riley/West record has 12 units, this formula returns 12:
With names in A2:A6, regions in B2:B6, units in C2:C6, Riley in E2, and West in F2, the formula returns 12: =INDEX(C2:C6,MATCH(1,(A2:A6=E2)*(B2:B6=F2),0))
In current Microsoft 365 Excel, enter this formula normally with Enter; dynamic-array calculation handles the range comparisons. Older Excel releases may require array-formula entry, and behavior can differ by version. If multiple rows meet both conditions, MATCH returns the first one. If uniqueness matters, verify that the source data has only one matching combination. For a simpler alternative, add a helper column that combines the fields, taking care to use a separator that cannot make different combinations look identical.
Handle missing values and common errors
The most frequent lookup error is #N/A, which means MATCH could not find a match under the specified conditions. Check for spelling differences, extra spaces, and values stored with different types—for example, a number in one range and text that looks like a number in another. Also check that the lookup value is actually inside the selected range and that the ranges are aligned.
If the worksheet should show a friendly message rather than #N/A, wrap the lookup in IFNA. For example, with names in A2:A6, departments in B2:B6, and the lookup value in E2, this returns the department when found and Not found when there is no exact match:
With names in A2:A6, departments in B2:B6, and a lookup value in E2, the formula returns the matching department or Not found: =IFNA(INDEX(B2:B6,MATCH(E2,A2:A6,0)),"Not found")
IFNA is narrower than IFERROR: it handles #N/A but leaves other errors visible. That can be helpful because an unexpected reference or calculation problem is not silently disguised as a missing record. A #REF! error often indicates a damaged or invalid reference, while an unexpected answer commonly points to misaligned ranges, duplicate keys, or an incorrect match type. During setup, test a key known to exist, one known not to exist, and a boundary value if the formula uses approximate matching.
INDEX MATCH compared with XLOOKUP
XLOOKUP is often the simpler choice for new workbooks when it is available. It can search one range and return a corresponding value from another, supports exact matching by default, and can search in either direction. XLOOKUP is available in Microsoft 365 and Excel 2021 or later, but it is not available in some older Excel versions. A workbook relying on it may therefore be less compatible with older installations.
INDEX MATCH remains valuable when you need compatibility with older Excel, want to see the separate position-finding and return steps, or are maintaining an established workbook. It also supports two-way lookups naturally by using one MATCH for each dimension. VLOOKUP remains familiar and can be suitable for straightforward tables where the lookup field is at the left, but it cannot return a value from a column to the left of that field. Choose based on the workbook’s users, Excel versions, and the shape of the data rather than assuming one function is best for every lookup.
Frequently Asked Questions
Is INDEX MATCH better than VLOOKUP?
It is more flexible about column direction and does not rely on a return-column number within a selected table. VLOOKUP can still be straightforward for simple tables whose lookup column is on the left and whose result is to the right.
Why does my INDEX MATCH formula return #N/A?
MATCH likely found no qualifying value. Check that the lookup value is in the search range, that exact-match formulas use 0, and that text, numbers, spaces, and spelling are consistent. For approximate matches, check the required sort order.
Does INDEX MATCH need sorted data?
No, not when MATCH uses 0 for an exact match. Approximate modes require an appropriately sorted lookup range: ascending for 1 and descending for -1.
Can INDEX MATCH return a value from a column on the left?
Yes. Set INDEX’s return range to the left-hand column and MATCH’s search range to the column containing the key. The ranges still need to correspond row by row.
Does INDEX MATCH work in older Excel versions?
INDEX and MATCH are available in many older Excel releases, which is one reason the combination is used in compatibility-conscious workbooks. Newer functions such as XLOOKUP are not available in every older version, so check the recipient’s Excel version when sharing a file.
What does the 0 mean in MATCH?
It selects exact-match behavior. For typical lookups by name, ID, or code, explicitly using 0 helps avoid the approximate-match behavior associated with other match types.
Related
- Excel Find the Minimum Date With Criteria
- Excel Find the Minimum Value in a Range With Criteria
- Excel Find Maximum Value Based on Criteria with MAXIFS
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Wildcard Text Search: Formulas and Examples GuideTo check whether text in A2 contains the search term in E2, use =ISNUMBER(SEARCH(E2,A2)). It returns TRUE when…
- Excel Formula for the Higher of Two Values: MAX or IFTo return the higher of two values in Excel, use the MAX function. If the values are in…
- Excel MATCH Function: Find a Value’s PositionExcel’s MATCH function finds a value in a range and returns its relative position, not the value itself.…
- Excel MID Formula: Extract Text by PositionUse Excel’s MID function to extract a specified number of characters from a text value, starting at a…
- Link Different Sheets in Excel with FormulasTo link a cell on one Excel sheet to a cell on another, enter a reference such as…
- Excel Text Left of a Character: Formulas and ExamplesTo return the text before a character in Excel, combine LEFT with FIND: if A2 contains INV-2048, enter…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.