The MATCH function in Excel helps you find the position of a value in a row or column. Instead of returning the actual value, MATCH returns the location number of where the value appears within a range. This makes it incredibly useful for lookups, finding positions, building dynamic formulas, and pairing with other functions like INDEX for advanced searches.
Businesses often use the MATCH function to locate products, employee IDs, sales regions, or inventory items within large spreadsheets.
In This Guide, You’ll Learn
- What the MATCH function does
- The syntax and arguments of MATCH
- How to find the position of exact values
- How to use approximate matching in Excel
- Common MATCH mistakes and how to avoid them
- Real-world examples using MATCH with business data
What is the MATCH Function?
The MATCH function searches for a value in a row or column and returns its relative position in the selected range.
For example, if the word “Laptop” appears as the third item in a list, MATCH will return 3.
MATCH Function Syntax
=MATCH(lookup_value, lookup_array, [match_type])Argument Definitions
- lookup_value – The value you want Excel to find. This can be a number, text value, cell reference, or logical value.
- lookup_array – The range of cells where Excel should search.
- [match_type] – Optional argument that controls how Excel matches values:
- 0 = Exact match (most common option)
- 1 = Largest value less than or equal to lookup value (ascending sort required)
- -1 = Smallest value greater than or equal to lookup value (descending sort required)
Important: Most Excel users should use 0 for exact matches.
Example Dataset Used in This Tutorial
You can download the sample data we’ll be using in this tutorial by clicking on this link—> MATCH Function Data
Find the Position of a Product Name
Suppose you have a list of products and want to know where “Keyboard” appears in the list.
Formula Used
=MATCH("Keyboard",A2:A6,0)
How This Formula Works
- Excel searches through cells A2:A6.
- It looks for an exact match to “Keyboard.”
- Since Keyboard appears third in the range, Excel returns 3.
Find an Employee ID Position
You can also use MATCH to locate employee IDs.
Formula Used
=MATCH(C2,A2:A7,0)
How This Formula Works
- Excel scans the employee ID column.
- It searches for the ID 1005.
- Since the ID is the fifth item in the range, Excel returns 5.
Use Approximate Match for Sales Targets
The MATCH function can also categorize values using approximate matches.
Imagine a commission system:
- 0 = Beginner
- 5000 = Intermediate
- 10000 = Advanced
- 20000 = Expert
You want to determine where a sales value of 12,000 falls.
Formula Used
=MATCH(C2,A2:A5,1)
How This Formula Works
- Excel searches an ascending list.
- MATCH uses 1 to find the largest number less than or equal to 12,000.
- Excel returns 3 because 10,000 is the closest qualifying value.
MATCH with Text Lookup
MATCH also works with text values such as department names.
Formula Used
=MATCH(C2,A2:A6,0)
How This Formula Works
- Excel searches the department list.
- It finds “IT” in the fourth position.
- MATCH returns 4.
Common MATCH Errors
Forgetting to Use Match Type 0
If you leave out the match type, Excel defaults to 1, which can return unexpected results.
Incorrect:
=MATCH("Keyboard",A2:A6)Better:
=MATCH("Keyboard",A2:A6,0)Using Approximate Match on Unsorted Data
If you use 1 or -1, your data must be sorted correctly.
- 1 requires ascending order
- -1 requires descending order
Searching for Missing Values
If Excel cannot find a match, it returns:
#N/AYou can handle this with IFERROR if needed.
Example Dataset Used in This Tutorial
You can download the sample data we’ll be using in this tutorial by clicking on this link—> MATCH Function Data
