How to use MATCH Functions in Excel – Complete Guide

# How to use MATCH Functions in Excel – Complete Guide

Last updated:
6th Jun, 2022
Views
7 Mins
View All

Excel is one of the most commonly used data science and data analytics tools. The MATCH function is one of the many useful features this powerful software offers. By enabling MATCH functions in your current position (or any cell of your choosing), you can locate values you are looking for inside a table, column, or row.

You can locate the exact value you are looking for and also the closest value to it. Additionally, one can also choose to use the INDEX function to reflect the value occupying the position of the row the function brings up or even certain associated values. Just by using MATCH functions in Excel on their own, you can identify the exact row the matching (or partially matching) value occupies inside a given range.

With MATCH functions, you can manually provide the input value for identifying a matching value in the array and allow the function to automatically locate matching values for whatever is the current value occupying your reference cell. For example, if you use the cell D4 for reference in the matching function instead of using a number or value inside the function, the function will automatically identify the row number occupied by a similar or identical value as D4. As soon as you change the value of D4, the function will again immediately retrieve the row number of the next closest value.

MATCH functions in Excel work with both alphabets and numbers while not being uppercase or lowercase sensitive, thus identifying the same combination of alphabets regardless of their case. This function is crucial for different automated workflows (Excel-based) and, in general, analytics.

Learn Data Science Courses online at upGrad

## Syntax

The syntax for MATCH functions is:

=MATCH (lookup_value, lookup_array, match_type)

For example, this is a MATCH function: =MATCH(E4,C2:C12,1)

Now, let’s break down the syntax of the MATCH formula in Excel.

• Lookup_value: This value is compulsory and must be the exact value or a value close to what you want to find in an array. For example, if you are looking for a number close to 2000 (i.e. 2010) inside a range, you should use 2000 as a reference point for your matching function. You can also use wildcards such as ‘*’ and  ‘?’ when looking for partial matches. The question mark helps match a single character, while the asterisk helps match a sequence or combination of characters.
• Lookup_array: This will represent the target range (or array) for finding the position of the matching value. For example, C2 to C20 is a range that will be represented as “C2:C20” inside the function.
• Match_type: This argument is optional in the MATCH formula in Excel but is set to 1 by default. There are three options to choose from when it comes to the Match_type
1. 0: This is used for an exact matching function where you want to find the position of the value in an unstructured array. An unstructured array would be a range where the values are not sorted in ascending or descending order.
2. 1: This is the default value of a MATCH function if you do not put any value as the Match_type. This will command the function to find the position of either the exact value or the next smallest value. For instance, if you use Lookup_value as ‘25’, in a range of cells having the values 11, 23, 32, 41, the function will bring up the cell’s position having ‘23’ inside. However, the array or the range must be sorted in ascending order (depending on their values).
3. -1: This is used for locating the exact value or the next largest value in an array. When using ‘-1’, the range must be sorted in descending values.

If you wish to allow the automatic retrieval of the value of a matched position (row), you can use the INDEX function along with the MATCH function. MATCH functions in Excel identify the position of the match while INDEX functions retrieve the value occupying that row in an array.

This is how an INDEX function is used with a MATCH function:

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

With the help of the INDEX MATCH formula in Excel, you can also implement a two-way lookup function, like so:

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

With the help of the INDEX MATCH formula in Excel, the MATCH function becomes more powerful and becomes capable of conducting more advanced multi-dimensional lookups based on multiple criteria. However, to use these more advanced functions of Excel, learning how to use the MATCH function effectively is crucial.

## Check our US - Data Science Programs

 Professional Certificate Program in Data Science and Business Analytics Master of Science in Data Science Master of Science in Data Science Advanced Certificate Program in Data Science Executive PG Program in Data Science Python Programming Bootcamp Professional Certificate Program in Data Science for Business Decision Making Advanced Program in Data Science

## Using MATCH functions in Excel

Now, let us learn how to use MATCH functions in Excel to locate numeric values or even text.

In this first example, the values in the range A1 to A3 are arranged in ascending order.

 A B C D 1 12 2 22 3 35

So, if we wish to locate the matching value for ‘25’, we will be using this MATCH formula in Excel:

=MATCH(25,A1:A3,1)

This will be what is returned:

This will return the value ‘2’ as that is the position of the row containing the closest number to ‘25’. There was no exact match, so 22 was identified by the function as it has a lower value than ‘25’ and because we used ‘1’ as our Match_type. Additionally, we have used ‘A1:A3’ as our Lookup_array as this is the range containing the numbers. If the array was sorted in descending order, we would have to use ‘-1’ in this function for the Match_type.

In the case of a negative approximate match type, a number bigger than ‘22’ or any Lookup_value would be identified. However, if we use ‘-1’ and the array is not in descending order, the matching function will return an error.

In the next example, let us try to locate the position of ‘Cookies’ among the three words occupying the cells inside the range of A1:A3.

 A B C D 1 Waffles 2 Cookies 3 Shake

This will be the formula:

When we locate text, we must keep the Lookup_value inside “ ” to allow the function to figure out the position of the text. The case does not matter in MATCH functions, as even without using “Cookies” (C in uppercase), and by using “cookies” (c in lowercase), we can still find the matching position. However, MATCH functions can be combined with an EXACT function to make the function case-sensitive and accurately find the exact combination of alphabets.

Here is one final example where we will try locating the location of the approximate value or the next larger number in an array sorted in descending order.

In this example, we will use 35 as the Lookup_value.

 A B C D 1 40 35 2 30 3 20

This will be the formula:

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

This will be what is returned:

40 will be chosen as the matching value as it is greater than 35 even though both 40 and 30 are the closest to 35. Also, if there are any issues with the order of the array or in the data, the MATCH function will return “#N/A”.

Now, let us take the instance where we do not numerically declare the  Lookup_value in the formula. We can also choose to mention the position of another cell to use that value as the active  Lookup_value in the formula. Since the value occupying D1 is 35, we can also use this formula:

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

Here, just D1 is replacing “35” in the formula.

## Read our Popular US - Data Science Articles

 Data Analysis Course with Certification JavaScript Free Online Course With Certification Most Asked Python Interview Questions & Answers Data Analyst Interview Questions and Answers Top Data Science Career Options in the USA SQL Vs MySQL – What’s The Difference An Ultimate Guide to Types of Data Python Developer Salary in the US Data Analyst Salary in the US: Average Salary

## Conclusion:

Learning the MATCH function is essential to use any INDEX MATCH formula in Excel for advanced lookups. Also, it is essential to remember that MATCH functions in Excel only work on the text with a maximum of 255 characters. In the case of duplicate values, the function retrieved the first match. For EXACT matches or when the Match_type is “0”, you can also use the wildcards in the Lookup_value

The MATCH function is crucial for Data Science and is extensively used with the INDEX function. A top-tier Data Analytics Program from Upgrad is bound to take you places in your career.

If you are already pursuing a career in this field but in a dilemma about what to do next in your career, you can find some of the best mentorship services by industry experts in upGrad

If you’d like to gain in-depth knowledge of Python, we recommend upGrad’s online Professional Certificate Program in Data Science and Business Analytics from the University of Maryland, which is one of the top 100 global universities in the world.

Blog Author
Director of Engineering @ upGrad. Motivated to leverage technology to solve problems. Seasoned leader for startups and fast moving orgs. Working on solving problems of scale and long term technology strategy.
Get Free Consultation

Select
Select Area of interest
Select Work Experience
By clicking 'Submit' you Agree to

#### Our Best Data Science Courses

11. Is the MATCH function case-sensitive?

No, the MATCH function is not case-sensitive.

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

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

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

## Suggested Blogs

5608
Python is considered one of the easiest programming languages. It is widely used for web development, gaming applications, data analytics and visualiz

08 Aug 2024

15656
Thanks to the big data revolution, the modern business world collects and analyzes millions of bytes of data every day. However, regardless of the bus

28 Aug 2023

5519
Data Science is now considered to be the future of technology. With its rapid emergence and innovation, the career prospects of this course are increa

14 Apr 2023

5474
Data Science projects are great for practicing and inheriting new data analysis skills to stay ahead of the competition and gain valuable experience.

07 Apr 2023

6575
Data refers to the collection of information that is gathered and translated for specific purposes. With over 2.5 quintillion data being produced ever

06 Apr 2023

5773
Data Science is now considered to be the future of technology. With its rapid emergence and innovation, the career prospects of this course are increa

05 Apr 2023

5385
A binary tree is a non-linear tree data structure that contains each node with a maximum of 2 children. The binary name suggests the number 2, so any

03 Apr 2023

6056
Python is an interpreted, high-level, object-oriented programming language and is prominently ranked as one of the top 5 most famous programming langu