The MATCH function is a handy feature in Excel that allows users to locate values inside a table, column, or row. It is similar to the INDEX function, if not the same. Using MATCH functions in Excel, the desired row and the matching (or partially matching) value inside a given range can be easily identified. However, the exact value cannot be located – so it shows the closest value.
Additionally, MATCH functions can provide the input value manually for identifying a matching value in the array. It allows the function to find matching values for the current value of the desired cell. For instance, if cell A5 is used for reference, the function automatically identifies the row number that the similar or identical value as A5 occupies instead of using a value or number in the function. Once the value of A5 changes, the function immediately retrieves the row number of the next value that is closest to it.
MATCH functions in Excel are not uppercase or lowercase sensitive and can work with both numbers and alphabets and identify identical alphabet combinations. This is an integral function for different automated Excel-based workflows and, in general, analytics as well. Knowledge of the MATCH function is essential in Data Science and Analytics.
Learn data science courses from the World’s top Universities. Earn Executive PG Programs, Advanced Certificate Programs, or Masters Programs to fast-track your career.
Explore our Popular Data Science Courses
The syntax for MATCH functions is as follows:
=MATCH (lookup_value, lookup_array, match_type)
This is an example of a MATCH function:-
Now, let us break down the syntax and discuss the three arguments that are incorporated into the MATCH formula in Excel.
- Lookup_value: This value is compulsory and must be the exact value or a value nearest to what you are looking for in an array. For example, if you are searching for a number close to 2000 inside a range, use 2000 as a reference point for the matching function. For partial matches, use wildcards like ‘* that helps match any character combination or sequence and ‘?’ that matches a single character.
- Lookup_array: This represents the target range or array for finding the matching value’s position. For example, C2 to C20 is a range, and it will be represented as “C2:C20” inside the function. This is also a compulsory component of the function.
- Match_type: There are three options in this argument in the MATCH formula in Excel, but the default setting is 1. There are three options –
- 0: This is used for an accurate matching function to find the value’s position in any unstructured array. An unstructured array is a range of values that are not sorted in any order.
- 1: This is the default value of a MATCH function if there is no input of any other value as the Match_type. This commands the function to look for the position of the accurate value or the second smallest value.
- -1: This locates the exact or second largest value in an array. When ‘-1’ is used, the range should be sorted in descending order.
This is an example of an INDEX function being used with a MATCH function:
An INDEX MATCH formula in Excel can also help implement a two-way lookup function. For example:
INDEX MATCH formula in Excel makes the MATCH function more effective. It gives it the ability to execute advanced multi-dimensional lookups based on several criteria.
Using MATCH functions in Excel
Learning how to use MATCH functions in Excel is essential for locating the position of numeric values or text. Let us look at the example below:-
This example shows the values in the range A1 to A3 arranged in ascending order.
So, if we wish to locate the matching value for ‘120’, we will be using this MATCH formula in Excel:
This will be what is returned: 2
The value ‘2’ will be the result as it is the position of the row with the closest number to ‘120’. No number exactly matches 120 and therefore is identified by the function because it has a lower value than ‘120’ and because ‘1’ was used as the Match_type. ‘A1:A3’ is the Lookup_array which is the range with the numbers. If the array were sorted in descending order, ‘-1’ would be used in the function for the Match_type.
When it comes to negative approximate match type, the number greater than ‘120’ or any Lookup_value would be shown. But there would be an error in the matching function if ‘-1’ is used, and the array is in ascending order.
In the following example, let us try to locate the position of ‘Camera’ amongst the three words in the cells inside the range of A1:A3.
This will be the formula:
For locating text, always keep the Lookup_value inside “” to determine the position of the text using the function. The MATCH function is not case-sensitive. The matching position can still be located without using “Camera” with C in uppercase and by using “camera” where C is in lowercase.
To make MATCH functions case-sensitive, it can be clubbed with an EXACT function to find the accurate alphabet combination. When the Match_type is “0” or for EXACT matches, always use wildcards in the Lookup_value.
Here is another example where the location of the approximate value or the second-largest number needs to be located in an array sorted in descending order.
In this example, we will use 25 as the Lookup_value.
This will be the formula:
This will be what is returned: 1
30 will be the matching value because it is greater than 25 despite both 30 and 20 being closest to 25. MATCH function will result in “#N/A” if there is an issue with the array order.
In any instance where the Lookup_value is not numerically declared in the formula. The position of another cell can also be mentioned for using that value as the active Lookup_value.
Since the value of the cell D1 is 25, the following formula should be used:
D1 is replacing “25” in the formula.
Read our popular Data Science Articles
Learning the MATCH function is crucial for executing any INDEX MATCH formula in Excel for advanced lookups. The use of the MATCH function is integral in Data Science and is combined with the INDEX function exclusively.
Executive Post Graduate Programme in Data Science is an excellent choice if you are looking for a premium course to learn about MATCH and other Excel functions.
1. Do MATCH functions have any limits?
Yes, MATCH functions have a limit. MATCH functions in Excel are only functional, with a maximum of 255 characters on the text. In the case of duplicate values, the function retrieved the first match.
2. Are MATCH functions capable of retrieving values and not just row positions?
MATCH functions can also retrieve associated (related) values or specific values if combined with other functions.
3. Can the MATCH function be used both horizontally and vertically?
Yes, it can be used horizontally and vertically if combined with the INDEX function.