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:
- lookup_value: The value you want to find in the array (e.g., a number, text, or logical value).
- lookup_array: The range of cells to search within.
- [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:
| Match Type | Behavior | Details |
|---|---|---|
| 1 | Approximate Match | Finds the largest value less than or equal to the lookup value. The array must be sorted in ascending order. |
| 0 | Exact Match | Finds the first value exactly equal to the lookup value. Sorting is not required. |
| -1 | Approximate Match | Finds the smallest value greater than or equal to the lookup value. The array must be sorted in descending order. |
| (omitted) | Approximate Match | Defaults to 1, performing as described for match_type = 1. |
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:
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).
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:
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).
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.
Result: 3 (as 40 is the smallest value ≥ 35, and it’s in the third position).
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:
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).
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.
=IFERROR(MATCH("Pineapple", A1:A5, 0), "Not Found")Result: "Not Found" (since "Pineapple" is not in the range).
