How to Use the MATCH Function in Excel

The MATCH function in Excel is a search tool that returns the position of a value in a range. Instead of returning the value itself, it tells you where the value is located in a list, row, or column. This is especially useful when you want to find the position of a number or text within a dataset.

---

What is the MATCH Function?

MATCH is short for match position. It looks through a range, finds your lookup value, and returns its position number (not the content). You can search for exact matches or the closest value.

MATCH is often used with the INDEX function to look up values, but it can also be used on its own.

---

Syntax of the MATCH Function:

=MATCH(lookup_value, lookup_array, [match_type])

• lookup_value: The value you want to find.
• lookup_array: The range of cells to search in.
• [match_type](optional):
  • 1 → Less than (values in ascending order)
  • 0 → Exact match (most common)
  • -1 → Greater than (values in descending order)

---

Where Can We Use the MATCH Function?

The MATCH function is helpful in scenarios such as:

• Finding Positions: Locate the position of a name, ID, or value in a list.
• Dynamic Lookups: Use with INDEX for advanced searches.
• Position-Based Calculations: Use the number position for further logic.
• Rank Checking: Find where a score stands in a list.
• Error Checking: Quickly see if a value exists in a dataset.

---

How to Use the MATCH Function in Different Methods

Here are four common methods to use MATCH in Excel:

---

Method 1: Exact Match Search

This method finds the exact position of a value.

Steps:

1. Enter your data:

A (Names)
John
Maria
Alex

2. In cell C1, type the name you want (e.g., Alex).
3. In cell D1, type: =MATCH(C1, A1:A3, 0)
4. Press Enter. The result (3) will appear because Alex is in the 3rd position.

---

Method 2: Approximate Match (Less Than)

Find the position of the largest value less than or equal to your lookup value.

Steps:

1. Enter your data:

A (Scores)
50
60
80

2. In C1, type a score (e.g., 75).
3. In D1, type: =MATCH(C1, A1:A3, 1)
4. Press Enter. The result (2) will appear because 60 is the largest number less than 75.

Note: Your list must be in ascending order for match_type 1.

---

Method 3: Approximate Match (Greater Than)

Find the position of the smallest value greater than or equal to your lookup value.

Steps:

1. Enter your data:

A (Scores)
80
60
50

2. In C1, type a score (e.g., 65).
3. In D1, type: =MATCH(C1, A1:A3, -1)
4. Press Enter. The result (1) will appear because 80 is the smallest number greater than 65.

Note: Your list must be in descending order for match_type -1.

---

Method 4: Using MATCH with INDEX

You can combine MATCH with INDEX to return the value from a position dynamically.

Steps:

1. Enter your data:

A (Names)
John
Maria
Alex

2. In C1, type the name you want (e.g., Maria).
3. In D1, type: =INDEX(A1:A3, MATCH(C1, A1:A3, 0))
4. Press Enter. The result will be “Maria”.

---

Live Example Showing Progress in Each Step

Data Setup:

A (Names)
John
Maria
Alex

Goal:
Find the position of a name and optionally return the name using INDEX.

Step 1: Enter names in A1:A3.
Step 2: Type a lookup name in C1.
Step 3: Use MATCH in D1 to get the position.
Step 4: Optionally, combine with INDEX to return the value.

Final Table:

Lookup Value	Position	Formula
Maria	2	=MATCH(C2, A1:A3, 0)
Alex	3	=MATCH(C3, A1:A3, 0)
John	John	=INDEX(A1:A3, MATCH(C4, A1:A3, 0))

---

Key Notes About the MATCH Function

• Returns Position, Not Value: Use INDEX to get the value.
• Match Types Require Sorting: 1 for ascending order, -1 for descending order.
• Case-Insensitive: MATCH ignores case differences.
• Error if Not Found: Will return #N/A if the value is not in the list. Use IFERROR to handle this.
• Flexible Use: Works with both numbers and text.

---

Conclusion

The MATCH function in Excel is a simple yet powerful tool for finding the position of a value in a list or range. Whether you’re checking where a score stands, locating a product in a list, or building dynamic lookups with INDEX, MATCH makes it quick and efficient.

By learning exact and approximate matches, and how to pair MATCH with INDEX, you can create smarter and more adaptable Excel formulas. Practice with different datasets to fully understand how MATCH works.