To return the index (position) of a match in Excel, use the MATCH function for most cases, or XMATCH if you have Excel 2021 or Microsoft 365. For example, =MATCH("Apple", A2:A10, 0) returns the position of the first “Apple” in the range A2:A10 (e.g., 3 if it’s the third cell). This article explains how both functions work, how to choose between them, common pitfalls, and practical examples.
What Does “Index of a Match” Mean?
In Excel, “index” refers to the position number of a cell within a range, counting from the top-left. For a vertical range like A2:A10, the index is the row offset from the first cell (A2 is 1, A3 is 2, etc.). For a horizontal range like B1:F1, the index is the column offset (B1 is 1, C1 is 2, etc.). The MATCH function returns this numeric position, which you can then use with INDEX to retrieve the actual value from another range.
How MATCH Works
The MATCH function has three arguments: MATCH(lookup_value, lookup_array, [match_type]). The lookup_value is what you’re searching for (text, number, or cell reference). The lookup_array is the range to search. The match_type is optional: 0 for exact match, 1 for approximate match (largest value less than or equal to lookup_value, requires ascending order), and -1 for approximate match (smallest value greater than or equal to lookup_value, requires descending order).
For example, with names in A2:A20 and you want the position of “John”, use =MATCH("John", A2:A20, 0). If John is in A7, the formula returns 6 (because A2 is the first cell, so A7 is the 6th). If you omit match_type, Excel defaults to 1, which assumes sorted data and can give wrong results with unsorted lists—so always specify 0 for exact matches.
XMATCH: The Modern Replacement
XMATCH, available in Excel 2021 and Microsoft 365, improves on MATCH in several ways. Its syntax is XMATCH(lookup_value, lookup_array, [match_mode], [search_mode]). The match_mode works like match_type but adds -1 for wildcard matching. The search_mode lets you search from the end (e.g., -1) or use binary search for speed. XMATCH also handles arrays natively and returns the position even if the lookup array is not a single row or column—it works with two-dimensional arrays, returning the relative position in row-major order (counting left-to-right, then top-to-bottom).
For example, to find the position of the last occurrence of “Red” in B2:B100, use =XMATCH("Red", B2:B100, 0, -1). XMATCH is generally preferred for new workbooks because it’s more flexible and less error-prone.
How to Choose Between MATCH and XMATCH
Use MATCH if you need compatibility with older Excel versions (2019 and earlier). Use XMATCH if you have Excel 2021 or Microsoft 365 and want advanced features like reverse search or wildcard matching. For most basic exact-match lookups, both work identically, but XMATCH is slightly faster and more intuitive. If you’re building a formula for others to use, check their Excel version first.
Common Problems and Solutions
- #N/A error: The lookup value isn’t found. Check for typos, extra spaces, or that the value is actually in the range. Use
=IFERROR(MATCH(...), "Not found")to handle gracefully. - Wrong result due to unsorted data: If you used match_type 1 or -1 without sorting, you get incorrect positions. Always use 0 for exact match unless you intentionally want approximate.
- Case sensitivity: MATCH and XMATCH are case-insensitive by default. For case-sensitive matching, use
EXACTin an array formula or useXMATCHwith theFINDfunction in an array context. - Wildcards: In MATCH, you can use * and ? in the lookup_value when match_type is 0. In XMATCH, set match_mode to 2 to enable wildcards. For example,
=XMATCH("A*", A2:A10, 2)finds the first cell starting with “A”. - Multiple matches: Both functions return the first match by default. To get the last match, use XMATCH with search_mode -1. To get all match positions, you’d need array formulas or FILTER.
Using INDEX and MATCH Together
The classic combination is INDEX(return_range, MATCH(lookup_value, lookup_range, 0)). For example, with product names in A2:A20 and prices in B2:B20, to get the price of “Widget”, use =INDEX(B2:B20, MATCH("Widget", A2:A20, 0)). This returns the price from the same row as the match. In Microsoft 365, you can replace this with XLOOKUP, which is simpler: =XLOOKUP("Widget", A2:A20, B2:B20). But INDEX/MATCH is still useful for horizontal lookups or when you need the position itself.
Excel vs. Google Sheets
In Google Sheets, both MATCH and XMATCH work similarly, but XMATCH is not available in all versions (it was added in 2022). Google Sheets also has INDEX and MATCH with the same syntax. One difference: in Google Sheets, MATCH with match_type 0 is case-insensitive, but you can use FIND in an array formula for case-sensitive. Also, Google Sheets’ XMATCH supports the same match_mode and search_mode arguments, but check your account’s feature rollout.
Frequently Asked Questions
How do I return the row number of a match?
Use =MATCH(lookup_value, range, 0) to get the relative position. To get the actual row number in the worksheet, add the starting row minus 1: =MATCH("Apple", A2:A10, 0) + 1 because A2 is row 2. For example, if the match is in A5, MATCH returns 4, and adding 1 gives row 5.
Can MATCH return multiple matches?
No, MATCH returns only the first match (or last with XMATCH search_mode -1). To return all positions, use an array formula like =FILTER(ROW(A2:A10)-1, A2:A10="Apple") in Excel 365, which spills the list of positions.
Why does MATCH return #N/A even though the value is there?
Common causes: extra spaces (e.g., “Apple ” vs “Apple”), non-printable characters, or the value is a number stored as text. Use =TRIM(A2) to clean data, or use =MATCH("Apple", A2:A10, 0) with the exact text. Also, ensure the range is correct and doesn’t include merged cells.
What is the difference between MATCH and XLOOKUP?
MATCH returns a position number; XLOOKUP returns the value from a return range. Use MATCH when you need the index for further calculations (like with INDEX), and XLOOKUP when you just want the corresponding value. XLOOKUP is simpler for most lookups and doesn’t require a separate INDEX.
Is MATCH case-sensitive?
No, by default MATCH and XMATCH ignore case. To make it case-sensitive, use an array formula with EXACT: =MATCH(TRUE, EXACT("Apple", A2:A10), 0) entered with Ctrl+Shift+Enter (in older Excel) or just Enter in Excel 365. This returns the position of the first exact case match.
Related
- Excel Month of Date as Text: 5 Easy Ways
- Free Excel Mileage Log Templates: 7 Real Options
- Excel Lowercase: Convert Text to Lowercase
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Concatenate Dates and Text in Excel: Formula GuideTo concatenate a date and text in Excel, convert the date to readable text with the TEXT function,…
- Average Time Between Dates in Excel: Formula & ExamplesTo calculate the average time between consecutive dates in Excel, subtract each date from the one below it,…
- NPV Formula Examples in Excel: Cash Flows ExplainedIn Excel, calculate net present value with =NPV(rate, future_cash_flows)+initial_cash_flow: enter the discount rate and future cash flows in…
- Excel Formula Budget Tracker: Setup & UseThe core of an Excel budget tracker is a simple formula: =SUM for totals and =B2-C2 for the…
- Excel Formula for File Path: Get the Workbook LocationIn Excel for Microsoft 365 on Windows, use =CELL("filename",A1) to return the saved workbook’s path, file name, and…
- How to Copy a Conditional Formatting Formula in ExcelTo copy a conditional formatting formula in Excel, copy a cell with the rule and use Home >…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.