How to Use MATCH in Excel
Returns the position of a value inside a range — the engine behind INDEX MATCH and most advanced lookups.
What is MATCH in Microsoft Excel?
MATCH does not return the value itself; it returns the position number where that value sits. Feed that number into INDEX and you have a lookup that works in any direction and survives column insertions. MATCH is also used on its own to find a row number for dynamic ranges and chart labels.
Formula Syntax
Arguments Breakdown
| Argument | Requirement | Purpose & Details |
|---|---|---|
| lookup_value | Required | The value whose position you want to find. |
| lookup_array | Required | A single row or column to search. A two-dimensional range returns #N/A. |
| match_type | Optional | 0 for exact match, 1 for the largest value less than or equal to the lookup (sorted ascending), -1 for the smallest value greater than or equal (sorted descending). |
When to Use MATCH (Real-World Business Scenarios)
- 1
Building INDEX MATCH when VLOOKUP's fixed column number keeps breaking.
- 2
Finding which row a value is on, so you can feed that position into INDEX, OFFSET or CHOOSE.
- 3
Locating the last occurrence of a value by searching from the bottom with match_type 0 and a reversed array.
Step-by-Step Practical Business Example
Suppose you have the following dataset in Excel cells A1:D4:
| Month | Sales |
|---|---|
| Jan | 45000 |
| Feb | 52000 |
| Mar | 38000 |
How it works: Searches the month column for 'Mar' with exact match and returns 3, because March is the third item in the range.
Common Errors & How to Fix Them
Why it happens: The value is not in the range, or match_type was omitted so Excel performed an approximate search on unsorted data.
Fix: Add the third argument as 0: =MATCH("Mar",A2:A4,0). Confirm there are no trailing spaces in the source column.
Why it happens: match_type 1 or -1 was used on data that is not sorted in the required direction.
Fix: Use 0 for exact matching, or sort the lookup column ascending before relying on approximate match.
Try MATCH Live in Your Browser
No need to launch desktop Excel. Practice formulas with real-time feedback in our HyperFormula sandbox.
Frequently Asked Questions (MATCH)
What does the MATCH formula do in Excel?
MATCH does not return the value itself; it returns the position number where that value sits. Feed that number into INDEX and you have a lookup that works in any direction and survives column insertions. MATCH is also used on its own to find a row number for dynamic ranges and chart labels.
What is the syntax for MATCH?
What are common mistakes when using MATCH?
#N/A: The value is not in the range, or match_type was omitted so Excel performed an approximate search on unsorted data. (Add the third argument as 0: =MATCH("Mar",A2:A4,0). Confirm there are no trailing spaces in the source column.) Wrong position returned: match_type 1 or -1 was used on data that is not sorted in the required direction. (Use 0 for exact matching, or sort the lookup column ascending before relying on approximate match.)
Related Excel Formulas
Keep building your formula toolkit with these lookup & reference functions.
- XLOOKUPThe modern, versatile replacement for VLOOKUP and HLOOKUP that searches in any direction without column index numbers.
- VLOOKUPThe industry-standard vertical lookup function that searches the first column of a table and returns a value in the same row.
- INDEX & MATCHThe robust two-part lookup formula that looks left, looks up, and never breaks when new columns are inserted.
- HLOOKUPThe horizontal twin of VLOOKUP: searches across the top row of a table and returns a value from a row below it.
- CHOOSEPicks one value from a list by position — a compact alternative to nested IF for fixed options.
- XMATCHThe modern replacement for MATCH with exact-match default, reverse search and wildcard support built in.
Master MATCH in Intermediate Excel
Learn MATCH along with our formula library, practice files, and verifiable certificates in our complete curriculum.