MATCH Function in Excel Tutorial

Excel Tutorial

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

ArgumentMeaning
lookup_valueThe value you want to search for.
lookup_arrayThe row or column in which Excel will search.
match_typeSpecifies how Excel should match the value. It is optional.

Understanding Match Types

The MATCH function supports three match types.

Match TypeMeaningRequirement
0Finds an exact match.Data can be in any order.
1Finds the largest value that is less than or equal to the lookup value.Data must be sorted in ascending order.
-1Finds 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:

  1. "E103" is the value Excel needs to find.
  2. A2:A6 is the selected employee ID range.
  3. 0 tells Excel to find an exact match.
  4. Excel checks the selected range and finds E103 in the third position.
  5. 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:A6 contains the employee names.
  • 0 specifies 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 IDProduct Name
P101Laptop
P102Keyboard
P103Mouse
P104Monitor
P105Printer

Excel Formula

=MATCH("Monitor",B2:B6,0)

Expected Result

Result
4

Explanation

  • "Monitor" is the lookup value.
  • B2:B6 is the product name range.
  • 0 searches 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 ValueResult
Finance4

Explanation

  • D2 contains the department name to search for.
  • A2:A6 contains the department list.
  • 0 specifies 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 NameMarks
Aarav78
Riya92
Mohit85
Simran88
Kabir75

Excel Formula

=MATCH(85,B2:B6,0)

Expected Result

Result
3

Explanation

  • 85 is the lookup value.
  • B2:B6 contains the marks.
  • 0 searches 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

  • 35000 is the lookup value.
  • A2:A6 contains sales targets in ascending order.
  • 1 tells 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

  • 35000 is the lookup value.
  • A2:A6 contains prices in descending order.
  • -1 tells 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:A6 contains product names.
  • 0 enables 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:

SymbolMeaning
*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 IDProduct NameCategoryPrice
P101LaptopElectronics55000
P102KeyboardAccessories1200
P103MouseAccessories600
P104MonitorElectronics12000
P105PrinterElectronics8500
P106ScannerElectronics9500

Your Tasks

  1. Find the position of Monitor in the Product Name column.
  2. Find the position of P105 in the Product ID column.
  3. Find the position of Accessories in the Category column using an exact match.
  4. Enter a product name in cell F2 and find its position using a cell reference.
  5. 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_value specifies what you want to find.
  • lookup_array specifies where Excel should search.
  • Match type 0 finds an exact match.
  • 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.
  • 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.