Contents
Excel MATCH Function
When to Use the Excel MATCH Function in Excel
The MATCH function1 in Excel comes in handy when you need to:
● Locate the position of a specific value in a range.
● Check if a value exists in a list or array.
● Determine the relative position of an item in a sorted or unsorted list.
What Excel MATCH Function Returns
The Excel MATCH function returns the relative position of a specified value in a range. The result can be one of three values:
● If the function finds an exact match, it returns the position of the item.
● If no match is found, it returns the #N/A error.
● If the match type is set to 1 and the data is not sorted, it returns the position of the nearest value less than or equal to the lookup value.
Syntax of Excel MATCH Function
The syntax of the Excel MATCH function is as follows:
=MATCH(lookup_value, lookup_array, [match_type])
Input Arguments
Here are the input arguments for the Excel MATCH function:
● lookup_value: The value you want to find in the lookup_array.
● lookup_array: The range of cells containing possible matches.
● match_type: An optional argument that specifies the type of match. It can be 1 (less than), 0 (exact match), or -1 (greater than).
Match Type Behavior:
- 1 or Omitted: If no exact match is found, the function returns the position of the closest value less than the lookup value. The lookup array must be sorted in ascending order.
- 0: If no exact match is found, the function returns an error. The lookup array does not need to be sorted.
- -1: If no exact match is found, the function returns the position of the closest value greater than the lookup value. The lookup array must be sorted in descending order.
Extra Notes
● MATCH is not case-sensitive.
● When no match is found, MATCH returns the #N/A error.
● MATCH is limited to working with text strings of up to 255 characters in length.
● If there are duplicate values, MATCH returns the position of the first occurrence.
● For match_type values of -1 or 1, it is essential for the lookup_array to be sorted, as mentioned earlier.
Examples of the Excel MATCH Function
Here are few examples of using the Excel MATCH function in Excel:
Example 1: Basic Usage
Suppose you have a list of fruits names in column A, and you want to find the position of the fruit “Pear” in the list. In cell D3, use the following formula:
Here is an explanation of the formula: =MATCH(D2,A3:A11,0)
- D2: This is the lookup_value. In this case, it is “Pear”, and the formula is trying to find the position of “Pear” within the specified range.
- A3:A11: This is the lookup_array. It represents the range of cells where Excel will search for the value “Pear”.
- 0: This is the match_type. The value 0 indicates that the function should find an exact match.
Example 2: Less Than Match
Suppose you have a set of numbers, and you want to find the position of the largest number less than the given lookup value. So, you need to select 1 for the match_type argument and write the MATCH function as shown below:
So, in the above example, we observe that the largest value below lookup_value (475), i.e., the number 400, is located at the fourth position. As mentioned earlier, we have arranged the lookup_array (A3:A12) in ascending order (smallest to largest) to obtain the correct result.
Example 3: Greater Than Match
Suppose you have a set of numbers, and you want to find the position of the smallest number greater than a given lookup value. So, you need to select -1 for the match_type argument and write the MATCH function as shown below:
So, in the above example, we observe that the smallest value above lookup_value (475), i.e., the number 500, is located at the sixth position. As mentioned earlier, we have arranged the lookup_array (A3:A12) in descending order (largest to smallest) to obtain the correct result.
Example 4: INDEX Function With MATCH Function
INDEX Function and MATCH Function are like a dynamic duo in Excel. They team up to search for data in a flexible way. They don’t mind how the columns are arranged, can hunt for data in both rows and columns, easily adapt to different criteria, handle unsorted data well, manage errors gracefully, and can be used as array formulas. This makes them versatile and powerful tools for handling various data situations in Excel.
I highly recommend reading my previous INDEX function post for a deeper understanding of this concept.
Practice Workbook
Download Free Excel File For PracticeConclusion
In conclusion, the Excel MATCH function is a valuable tool for locating specific values in a range. Whether you are working with lists of data, managing inventories, or analyzing information, the MATCH function can streamline your tasks and improve your Excel proficiency.
As with any Excel function, it’s essential to understand the syntax and experiment with different scenarios to master its usage. Start incorporating the MATCH function into your Excel projects today to enhance your data analysis and decision-making capabilities.
Frequently Asked Questions (FAQs)
Q1: Can the MATCH function be used to find the position of the last occurrence of a value?
A1: No, the MATCH function returns the position of the first occurrence of a value.
Q2: Is the MATCH function case-sensitive?
A2: No, the MATCH function is not case-sensitive.
Q3: What does the #N/A error mean in the context of the MATCH function?
A3: The #N/A error is returned when no match is found for the specified lookup value.
Q4: Can the Excel MATCH function handle arrays as lookup arrays?
A4: Yes, the Excel MATCH function can handle arrays as lookup arrays, making it versatile for various data structures.
Other Related Excel Functions
Mastering Excel VLOOKUP Function: A Comprehensive Guide to Enhance Your Data Analysis Skills
Mastering Excel FILTER Function: A Comprehensive Guide for Data Filtering
Join me on Instagram, YouTube and WhatApp Channel for your daily dose of valuable tips and tricks! Catch insightful videos that will enhance your knowledge and skills. Don’t miss out – follow me now!