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.
- 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.
- 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).
- -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.
Our learners also read: Learn Python Online for Free
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
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: 2
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:
=MATCH(“Cookies”,A1:A3)
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: 1
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
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.
![13 Exciting Data Science Project Ideas & Topics for Beginners in US [2024]](/__khugblog-next/image/?url=https%3A%2F%2Fd14b9ctw0m6fid.cloudfront.net%2Fhttps%3A%2F%2F65.0.10.158%2Fblog%2Fwp-content%2Fuploads%2F2019%2F07%2FBlog_FI_July_upGrads-Knowledge-base.png&w=3840&q=75)
