Introduction
The MATCH function is a useful Excel lookup function that helps you find the position of a specific value in a row or column. For example, if you want to know where a particular employee, product, or department appears in a list, MATCH can return its position.
What Is the MATCH Function in Excel?
The MATCH function searches for a specific value in a selected range and returns its relative position.
Let’s understand with a simple example.
Suppose you have this product list:
| Product |
|---|
| Laptop |
| Keyboard |
| Mouse |
| Monitor |
| Printer |
If you want to find the position of Mouse, the MATCH function returns 3 because Mouse is the third item in the selected range.
Remember: MATCH returns the position of a value, not the actual value itself.
MATCH Function Syntax
=MATCH(lookup_value, lookup_array, [match_type])
Understanding the Arguments
| Argument | Meaning |
|---|---|
| lookup_value | The value you want to search for. |
| lookup_array | The row or column in which Excel will search. |
| match_type | Specifies how Excel should match the value. It is optional. |
Understanding Match Types
The MATCH function supports three match types.
| Match Type | Meaning | Requirement |
|---|---|---|
| 0 | Finds an exact match. | Data can be in any order. |
| 1 | Finds the largest value that is less than or equal to the lookup value. | Data must be sorted in ascending order. |
| -1 | Finds the smallest value that is greater than or equal to the lookup value. | Data must be sorted in descending order. |
Important: If you omit the match_type argument, Excel uses 1 by default. For most beginner lookup exercises, use 0 when you need an exact match.
How Does the MATCH Function Work?
Suppose you have the following employee list:
| Employee ID |
|---|
| E101 |
| E102 |
| E103 |
| E104 |
| E105 |
Now use this formula:
=MATCH("E103",A2:A6,0)
Let’s understand how Excel evaluates it:
"E103"is the value Excel needs to find.A2:A6is the selected employee ID range.0tells Excel to find an exact match.- Excel checks the selected range and finds E103 in the third position.
- The result is
3.
Expected Result: 3
Notice that Excel returns position 3, not worksheet row number 4. MATCH counts positions within the selected range.
Practical Examples of MATCH Function in Excel
Let’s practice MATCH using different situations.
Question 1: Find the Position of an Employee
Problem Statement
An HR department maintains a list of employee names. The HR manager wants to find the position of Priya in the employee list.
Data Table
| Employee Name |
|---|
| Rahul |
| Aman |
| Priya |
| Neha |
| Karan |
Excel Formula
=MATCH("Priya",A2:A6,0)
Expected Result
| Result |
|---|
| 3 |
Explanation
"Priya"is the name Excel needs to find.A2:A6contains the employee names.0specifies an exact match.- Priya is the third name in the selected range.
Concept Covered
Finding the relative position of a text value using an exact match.
Question 2: Find the Position of a Product
Problem Statement
An electronics store maintains a product list. The manager wants to find the position of Monitor.
Data Table
| Product ID | Product Name |
|---|---|
| P101 | Laptop |
| P102 | Keyboard |
| P103 | Mouse |
| P104 | Monitor |
| P105 | Printer |
Excel Formula
=MATCH("Monitor",B2:B6,0)
Expected Result
| Result |
|---|
| 4 |
Explanation
"Monitor"is the lookup value.B2:B6is the product name range.0searches for an exact match.- Monitor appears in the fourth position.
Concept Covered
Finding a product’s position in a list.
Question 3: Find the Position Using a Cell Reference
Problem Statement
A company maintains a department list. Instead of typing the department name directly into the formula, the manager wants to enter the department name in another cell and use that cell as the lookup value.
Data Table
| Department |
|---|
| IT |
| HR |
| Sales |
| Finance |
| Marketing |
Assume cell D2 contains Finance.
Excel Formula
=MATCH(D2,A2:A6,0)
Expected Result
| Lookup Value | Result |
|---|---|
| Finance | 4 |
Explanation
D2contains the department name to search for.A2:A6contains the department list.0specifies an exact match.- Finance is found at position 4.
Concept Covered
Using a cell reference as the lookup value makes the formula easier to update. You can change the value in D2 without rewriting the formula.
Question 4: Find the Position of a Student’s Marks
Problem Statement
A teacher maintains student marks in a list. The teacher wants to find the position of the student who scored 85 marks.
Data Table
| Student Name | Marks |
|---|---|
| Aarav | 78 |
| Riya | 92 |
| Mohit | 85 |
| Simran | 88 |
| Kabir | 75 |
Excel Formula
=MATCH(85,B2:B6,0)
Expected Result
| Result |
|---|
| 3 |
Explanation
85is the lookup value.B2:B6contains the marks.0searches for an exact match.- Excel finds 85 at the third position.
Concept Covered
MATCH can search for numbers as well as text.
Question 5: Find a Position Using an Approximate Match
Problem Statement
A company maintains sales targets in ascending order. The manager wants to find the position of the largest target that is less than or equal to 35000.
Data Table
| Sales Target |
|---|
| 10000 |
| 20000 |
| 30000 |
| 40000 |
| 50000 |
Excel Formula
=MATCH(35000,A2:A6,1)
Expected Result
| Result |
|---|
| 3 |
Explanation
35000is the lookup value.A2:A6contains sales targets in ascending order.1tells Excel to find the largest value that is less than or equal to 35000.- The matching value is 30000, which is at position 3.
Concept Covered
Using match_type 1 for approximate matching in ascending data.
Remember: The lookup range must be sorted in ascending order when using match_type 1.
Question 6: Find a Position Using Descending Data
Problem Statement
A company maintains product prices in descending order. The manager wants to find the position of the smallest price that is greater than or equal to 35000.
Data Table
| Product Price |
|---|
| 60000 |
| 50000 |
| 40000 |
| 30000 |
| 20000 |
Excel Formula
=MATCH(35000,A2:A6,-1)
Expected Result
| Result |
|---|
| 3 |
Explanation
35000is the lookup value.A2:A6contains prices in descending order.-1tells Excel to find the smallest value that is greater than or equal to 35000.- Excel selects 40000, which is in position 3.
Concept Covered
Using match_type -1 for approximate matching in descending data.
Remember: The lookup range must be sorted in descending order when using match_type -1.
Question 7: Find a Product Using a Wildcard
Problem Statement
A store maintains product names. The manager remembers that the product name starts with the word Laptop but does not remember the complete name. The manager wants to find its position.
Data Table
| Product Name |
|---|
| Laptop Dell |
| Keyboard Logitech |
| Laptop HP |
| Mouse Wireless |
| Monitor Samsung |
Excel Formula
=MATCH("Laptop*",A2:A6,0)
Expected Result
| Result |
|---|
| 1 |
Explanation
"Laptop*"searches for a text value beginning with Laptop.A2:A6contains product names.0enables exact-match mode, which also supports wildcard characters for text.- The first matching product is Laptop Dell, located at position 1.
Concept Covered
Using the asterisk (*) wildcard to match text that begins with a particular word.
Wildcard symbols:
| Symbol | Meaning |
|---|---|
* | Matches any number of characters. |
? | Matches exactly one character. |
~ | Searches for an actual * or ? character. |
Student Practice Tasks
Try these questions yourself using the MATCH function.
Task 1: Employee Position
| Employee |
|---|
| Amit |
| Rahul |
| Neha |
| Priya |
| Karan |
Find the position of Neha.
Task 2: Product Position
| Product |
|---|
| Laptop |
| Mobile |
| Tablet |
| Printer |
| Monitor |
Find the position of Tablet.
Task 3: Department Position
| Department |
|---|
| IT |
| HR |
| Finance |
| Sales |
| Marketing |
Find the position of Sales using a cell reference containing the department name.
Task 4: Approximate Match
| Sales Target |
|---|
| 5000 |
| 10000 |
| 15000 |
| 20000 |
| 25000 |
Find the position of the largest target that is less than or equal to 18000. Use match_type 1.
Final Student Challenge: Build a Product Position Finder
Create the following product list in Excel.
| Product ID | Product Name | Category | Price |
|---|---|---|---|
| P101 | Laptop | Electronics | 55000 |
| P102 | Keyboard | Accessories | 1200 |
| P103 | Mouse | Accessories | 600 |
| P104 | Monitor | Electronics | 12000 |
| P105 | Printer | Electronics | 8500 |
| P106 | Scanner | Electronics | 9500 |
Your Tasks
- Find the position of Monitor in the Product Name column.
- Find the position of P105 in the Product ID column.
- Find the position of Accessories in the Category column using an exact match.
- Enter a product name in cell F2 and find its position using a cell reference.
- Use a wildcard to find the first product whose name starts with the letter P.
Challenge Goal: Create a simple product position finder that returns the relative position of a product without manually searching through the list.
Key Takeaways
- MATCH searches for a value in a selected row or column.
- It returns the relative position, not the actual matching value.
lookup_valuespecifies what you want to find.lookup_arrayspecifies where Excel should search.- Match type
0finds an exact match. - Match type
1finds the largest value less than or equal to the lookup value in ascending data. - Match type
-1finds the smallest value greater than or equal to the lookup value in descending data. - MATCH can search for text, numbers, and values supplied through cell references.
- Wildcards can be used with exact-match text searches.
Frequently Asked Questions (FAQs)
1. What is the MATCH function in Excel?
The MATCH function searches for a value in a selected range and returns its relative position.
2. Does MATCH return the actual value?
No. MATCH returns the position of the matching value, not the value itself.
3. What is the default match type in MATCH?
The default match type is 1, which performs an approximate match using ascending-sorted data.
4. What does 0 mean in the MATCH function?
The value 0 tells Excel to find an exact match. It is commonly used when searching for employee names, product IDs, and department names.
5. What is the difference between match_type 1 and -1?
Match type 1 finds the largest value less than or equal to the lookup value in ascending data. Match type -1 finds the smallest value greater than or equal to the lookup value in descending data.
6. Can MATCH search for text values?
Yes. MATCH can search for names, departments, products, employee IDs, and other text values.
7. Can MATCH use a cell reference?
Yes. You can enter the lookup value in a cell and use that cell reference as the first argument.
8. Can MATCH search using wildcard characters?
Yes. When using match_type 0, MATCH supports * and ? wildcards for text searches.
9. What happens if MATCH cannot find a value?
If Excel cannot find a matching value, MATCH returns the #N/A error.
10. What is the difference between INDEX and MATCH?
INDEX returns a value from a specified position in a range, while MATCH finds the position of a specified value in a range.

