MATCH function

The MATCH function in Excel is used to search for a specified value in a range of cells and return the relative position of that value within the range. It is commonly used to find the position of a value in a column or row, and is often used in combination with other functions like INDEX for more complex lookups.

Syntax:

=MATCH(lookup_value, lookup_array, [match_type])

• lookup_value: This is the value you want to search for. It can be a number, text, or logical value.
• lookup_array: This is the range of cells where you want to search for the lookup_value. It can be a row, column, or a single row/column of data.
• [match_type] (optional): This specifies how Excel should match the lookup_value with the values in the lookup_array:
  • 1 (or omitted): Finds the largest value that is less than or equal to lookup_value. The lookup_array must be sorted in ascending order.
  • 0: Finds the first exact match. The lookup_array does not need to be sorted.
  • -1: Finds the smallest value that is greater than or equal to lookup_value. The lookup_array must be sorted in descending order.

Example 1: Basic MATCH Function (Exact Match)

If you have the following list of names in column A:

You want to find the position of “Steve” in the list. Use the formula:

=MATCH("Steve", A1:A4, 0)

This will return 3, since “Steve” is the third item in the range A1:A4.

Example 2: Using MATCH with Approximate Match

Suppose you have a list of numbers and you want to find the position of a value that is smaller than or equal to your lookup value. Your data looks like this:

To find the position of the value that is less than or equal to 35 (an approximate match), use:

=MATCH(35, A1:A5, 1)

This will return 3, because 30 is the largest number that is less than or equal to 35, and it is in the third position in the range.

Example 3: Using MATCH with Sorted Data for Descending Order

If you have the same list of numbers, but now you want to find the position of the smallest value that is greater than or equal to 35 in a descending list:

Use this formula:

=MATCH(35, A1:A5, -1)

This will return 2, because 40 is the smallest value greater than or equal to 35, and it is in the second position in the range.

Example 4: MATCH with Text Lookup

MATCH can also be used to find the position of text. For example, if you want to find the position of “Alice” in the list of names:

Use this formula:

=MATCH("Alice", A1:A4, 0)

This will return 4, since “Alice” is the fourth item in the range.

Example 5: Using MATCH with INDEX for Lookup

You can use MATCH in combination with INDEX to return the actual value that corresponds to a specific position. Suppose you have the following table of products and their prices:

If you want to find the price of “Orange”, use MATCH and INDEX together:

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

Here’s how it works:

• MATCH("Orange", A1:A5, 0) finds the position of “Orange” in A1:A5, which is 4.
• INDEX(B1:B5, 4) returns the value in the fourth position of B1:B5, which is 1.2.

Benefits of MATCH:

• Efficient Search: MATCH is useful for finding the position of a value in a large dataset quickly and can be used with other functions like INDEX and VLOOKUP to build more complex lookups.
• Flexible Lookup Options: With the match_type argument, MATCH can be customized for exact, approximate, or descending order lookups.
• Text and Numeric Support: You can use MATCH to search for both text and numeric values, making it versatile for various types of data.

Use Cases:

• Dynamic Data Lookup: When you want to dynamically find a value’s position within a range and use that position in other functions.
• Advanced Lookups: Combining MATCH with INDEX for more flexible lookups that don’t require sorted data or column-based restrictions.
• Sorting and Ranking: Use MATCH to determine the position of items in a sorted list or ranking.

Limitations:

• Does Not Return the Value: MATCH only returns the position of the value, not the value itself. You typically need to combine it with another function (like INDEX) to retrieve the actual value.
• Sorted Data Requirement: When using 1 or -1 as the match type, the lookup_array must be sorted in ascending or descending order respectively. If the data is unsorted, you should use 0 for an exact match.
• Error Handling: If MATCH cannot find the value, it returns an error (#N/A). You can handle this with functions like IFERROR.

Alternative Functions:

• VLOOKUP and HLOOKUP: If you need to find values based on their position and return a corresponding value from a different column or row, you can use VLOOKUP or HLOOKUP.
• INDEX: Use INDEX when you need to return the value directly by specifying the row or column number.
• XLOOKUP: A more modern alternative to VLOOKUP and HLOOKUP, which eliminates some of the limitations of older lookup functions.

In summary, the MATCH function is a powerful tool for searching a range of cells and finding the position of a specific value. It is particularly useful when combined with other functions like INDEX or VLOOKUP for more complex lookups and dynamic data analysis.