How to use MATCH Functions in Excel?

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

Syntax

The syntax for MATCH functions is as follows:

=MATCH (lookup_value, lookup_array, match_type)

This is an example of a MATCH function:-

=MATCH(E4,C2:C12,1) 

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 –

1. 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. 
2. 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. 
3. -1: This locates the exact or second largest value in an array. When ‘-1’ is used, the range should be sorted in descending order.

MATCH Functions In Excel: Types 

The various types of MATCH functions in Excel are as follows: 

1. Exact MATCH: In cases when the match type is zero, the MATCH function executes an exact match. 
2. Approximate MATCH: When the MATCH type is set to 1, MATCH will make an approximation match on values sorted A-Z, locating the greatest value less than or equal to the lookup value. The MATCH function in Excel returns a match that is roughly 7. 
3. Wildcard MATCH: When the match type is zero, the MATCH function can carry out a match utilizing wildcards. When using wildcards, the MATCH function returns “pq” as the result. 

This is an example of an INDEX function being used with a MATCH function:

=INDEX(C4:F8,MATCH(G5,B4:B10,FALSE),2)

An INDEX MATCH formula in Excel can also help implement a two-way lookup function. For example:

=INDEX(B5:H9,MATCH(I5,D5:D9,1),MATCH(I8,B4:D4,1))

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. 

Let’s learn more about the INDEX MATCH formula in Excel as experts now consider it a highly significant function for managing huge company datasets. 

INDEX MATCH Formula In Excel

The INDEX and MATCH functions are combined in the INDEX MATCH formula in Excel. Based on the provided row and column numbers, the INDEX function searches for a value in an array. The MATCH function provides these column and row numbers.

The Benefit of Combining MATCH Within an INDEX 

The MATCH function, when used with an INDEX function, enables dynamic data retrieval based on predefined search parameters. Let’s say you have a table with a list of fruits and their respective pricing. You wish to get the cost of a certain fruit, let’s say an “Apple,” from the table. 

Finding the row in the table that says “Apple manually” and then looking for the price in the associated column are two ways to accomplish this. Nevertheless, the operation becomes error-prone and time-consuming when you have to deal with a vast dataset of rows and columns. 

Instead, you can leverage the INDEX function to extract the price from the appropriate column after using the MATCH function to locate the “Apple” row number in the table. The INDEX MATCH formula in Excel will look something like this: 

=INDEX(B2:E6,MATCH(“Apple”,A2:A6,0),3)

Tip: The XMATCH function, a more advanced version of the MATCH formula in Excel, is available in Excel 2021 and Excel 365.

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:

=MATCH(120,A1:A3,1)

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:

=MATCH(“Camera”,A1:A3)

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:

=MATCH(25,B1:B3,-1)

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:

=MATCH(D1,B1:B3,-1)

D1 is replacing “25” in the formula.

MATCH Function In Excel: Properties 

• Since it does not recognize uppercase and lowercase letters differently, it is not case-sensitive. 
• It gives back where the “lookup_value” is located in relation to the “lookup_array.” 
• It functions with one-dimensional arrays or ranges that can be horizontal or vertical. 
• When a “lookup_value” appears more than once in a “lookup_array,” it delivers the location of the first precise match. 
• The question mark (?) and asterisk (*), which are wildcard characters, can be used for partial matches if the “lookup_value” is text-based.

Things To Know About MATCH Functions in Excel

As you’ve just seen, it’s simple to use the MATCH functions in Excel. However, you must stay aware of a few things, just like you do for any other Excel function. 

• The MATCH function doesn’t return the lookup value. Instead, it returns the relative location of the lookup value in the array. 
• The MATCH formula is case-insensitive, especially when you work with text data. This implies MATCH functions never differentiate between the uppercase and lowercase characters. 
• The function allows partial matches with wildcards (* or?) and approximate and exact matching. 
• The lookup value’s position in the lookup array is returned if there are multiple instances of the lookup value. 
• The #N/A error is returned if the lookup value cannot be found in the lookup array. 

Read our popular Data Science Articles

Conclusion

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.