How to use MATCH Functions in Excel?

# How to use MATCH Functions in Excel?

Last updated:
26th Jun, 2023
Views
8 Mins
View All

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

 Executive Post Graduate Programme in Data Science from IIITB Professional Certificate Program in Data Science for Business Decision Making Master of Science in Data Science from University of Arizona Advanced Certificate Programme in Data Science from IIITB Professional Certificate Program in Data Science and Business Analytics from University of Maryland 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.

 A B C D 1 54 2 124 3 345

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.

 A B C D 1 Lights 2 Camera 3 Action

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.

 A B C D 1 30 25 2 20 3 10

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

 Data Science Career Path: A Comprehensive Career Guide Data Science Career Growth: The Future of Work is here Why is Data Science Important? 8 Ways Data Science Brings Value to the Business Relevance of Data Science for Managers The Ultimate Data Science Cheat Sheet Every Data Scientists Should Have Top 6 Reasons Why You Should Become a Data Scientist A Day in the Life of Data Scientist: What do they do? Myth Busted: Data Science doesn’t need Coding Business Intelligence vs Data Science: What are the differences?

## 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.

#### Rohit Sharma

Blog Author
Rohit Sharma is the Program Director for the UpGrad-IIIT Bangalore, PG Diploma Data Analytics Program.
Get Free Consultation

Select Course
Select
By clicking 'Submit' you Agree to

#### Data Science Skills to Master

11. 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.

22. 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.

33. Can the MATCH function be used both horizontally and vertically?

Yes, it can be used horizontally and vertically if combined with the INDEX function.

## Suggested Blogs

20807
Attending a PySpark interview and wondering what are all the questions and discussions you will go through? Before attending a PySpark interview, it’s

05 Mar 2024

5061
Data science is an important part of many industries today. Having worked as a data scientist for several years, I have witnessed the massive amounts

by Harish K

28 Feb 2024

5148
Data science training is one of the most hyped skills in today’s world. Based on my experience as a data scientist, it’s evident that we are in

by Harish K

28 Feb 2024

5074
A data science course syllabus covers several basic and advanced concepts of statistics, data analytics, machine learning, and programming languages.

by Harish K

28 Feb 2024

17571
Python is one of the most popular programming languages. Despite a transition full of ups and downs from the Python 2 version to Python 3, the Object-

27 Feb 2024

10750
Introduction Data mining is the process in which information that was previously unknown, which could be potentially very useful, is extracted from a

27 Feb 2024

80535
What is a Data Analytics Lifecycle? Data is crucial in today’s digital world. As it gets created, consumed, tested, processed, and reused, data goes

19 Feb 2024

138919
The arrangement of data in a preferred order is called sorting in the data structure. By sorting data, it is easier to search through it quickly and e