How To Use MATCH Function in Excel (With Examples)

Finding the right data in large spreadsheets can often feel like searching for a needle in a haystack. This is where the MATCH function in Excel proves invaluable. The MATCH function helps you locate the position of a specific value within a row or column, making it a cornerstone of efficient data management and analysis.

In this guide, we’ll explore how to use MATCH in Excel, discuss its syntax, and walk through MATCH Excel examples that showcase its practical applications. Whether you’re working on a project that involves dynamic lookups or need to pair it with other Excel lookup functions, the MATCH function simplifies complex tasks and empowers you to handle data more effectively.

MATCH Function in Excel

What is the MATCH Function in Excel

The MATCH function returns the position of a value in a row, column, or table, rather than the actual value itself. For Example, if you’re looking for the number “50” in a list of numbers and it’s the 5th item, the function will return the number 5.

Syntax of the MATCH Function

=MATCH(lookup_value, lookup_array, [match_type])

Here, the [match_type] value denotes whether the user wants an exact match or an approximate match.

Arguments:

1. lookup_value: The value you want to find in the array (e.g., a number, text, or logical value).
2. lookup_array: The range of cells to search within.
3. [match_type] (optional):
   • 1 (default): Finds the largest value less than or equal to the lookup_value. The array must be sorted in ascending order.
   • 0: Finds an exact match.
   • -1: Finds the smallest value greater than or equal to the lookup_value. The array must be sorted in descending order.

Key Features of the MATCH Function

• Versatile Matching: Supports exact matches and two types of approximate matches.
• Sorting Requirements: For approximate matches (match_type 1 or -1), the array must be sorted in ascending or descending order.
• Dynamic Use with INDEX: Often combined with the INDEX function for dynamic lookups.

How Does the MATCH Function Work

• The MATCH function scans the range (lookup_array) for the value (lookup_value).
• Based on the specified match_type, it identifies the relative position of the value within the array.
• The result is the index (position) of the value in the range.

Understanding Match Type in the MATCH Function

The match_type argument in the MATCH function is optional. If not specified, it defaults to 1 (exact match or the next smallest value). Depending on the value provided, MATCH can perform either an exact or an approximate match. Here’s how each match type works:

Key Points:

• 1 and -1: Often referred to as “approximate matches,” but the MATCH function will still return an exact match if one exists.
• Sorting: Proper sorting of the lookup array is crucial for match_type values 1 and -1.
• Default: If match_type is omitted, Excel assumes it is 1.

These rules ensure flexibility in handling sorted and unsorted data, depending on the use case.

How to Use the MATCH Formula

Here’s a step-by-step breakdown of using the MATCH function, along with examples.

Example 1: Exact Match with MATCH

When you need to find the exact position of a value in a range, set match_type to 0.

Example:

You have the following data in A1:A5:

Enter the Data into the Sheet

If you want to find the position of “Cherry”:

=MATCH("Cherry", A1:A5, 0)

Result: 3 (since “Cherry” is the third item in the range).

Exact Match with MATCH

Example 2: Approximate Match (Ascending Order)

When the data is sorted in ascending order, you can use match_type set to 1 to find the largest value less than or equal to the lookup value.

Example:

You have the following numeric data in B1:B5:

Enter the Data into the Sheet

If you want to find the position of the largest value less than or equal to 35:

=MATCH(35, B1:B5, 1)

Result: 3 (as 30 is the largest value ≤ 35, and it’s in the third position).

Preview Results

Example 3: Approximate Match (Descending Order)

When the data is sorted in descending order, you can use match_type set to -1 to find the smallest value greater than or equal to the lookup value.

Approximate Match

Result: 3 (as 40 is the smallest value ≥ 35, and it’s in the third position).

Approximate Match (Descending Order)

Example 4: Using MATCH with INDEX for Dynamic Lookups

The MATCH function is often paired with the INDEX Function to perform dynamic lookups.

Let’s say, You have the following data:

Enter the Data into the Sheet

If you want to find the price of “Cherry” dynamically:

=INDEX(B1:B5, MATCH("Cherry", A1:A5, 0))

Result: 2.5 (MATCH finds the position of “Cherry,” and INDEX retrieves the corresponding value from column B).

Enter the Data>> Use the Formula

Example 5: Handling Errors with MATCH

If the MATCH function doesn’t find the lookup value, it returns the #N/A error. To avoid this, you can use the IFERROR function.

Example:

Below is the given data with few fruits name.

Enter the Data into the Sheet

=IFERROR(MATCH("Pineapple", A1:A5, 0), "Not Found")

Result: “Not Found” (since “Pineapple” is not in the range).

Enter the Data into the Sheet>> Enter the Formula

Conclusion

The MATCH function is a versatile tool in Excel for locating the position of values in a row or column. It plays a crucial role in dynamic lookups, especially when combined with the INDEX function, creating powerful solutions like INDEX MATCH Excel. From finding exact matches to working with approximate matches in sorted arrays, the MATCH function simplifies data retrieval and boosts productivity.

By mastering how to use INDEX and MATCH in Excel, you’ll enhance your ability to analyze and organize data efficiently, ensuring accuracy and flexibility in your spreadsheets. Add the MATCH function to your Excel arsenal today and unlock its full potential!

tenacious39

Follow

Improve

Article Tags :

• Excel
• Microsoft Office
• News
• Excel-basics
• ExcelGuide

+1 More