How to use MATCH Function in Excel?

Wondering, "what is the MATCH Function in Excel?” This Function in Excel is a powerful and versatile tool that can help you find the position of a value in a range of cells. It can also be combined with other functions, such as INDEX, VLOOKUP, or HLOOKUP, to perform more complex tasks.  This blog will explore the MATCH Function in more detail. We will also look at the syntax, arguments, examples, and tips of the MATCH Function in Excel that can help you use it effectively.

Table of Contents

1) What is MATCH Function in Excel?

       a) MATCH Formula

2) How to use MATCH Function in Excel? 

       a) Example

3) MATCH Function in Excel - Key pointers 

4) Conclusion
 

What is MATCH Function in Excel?

A lookup/reference function called MATCH is a built-in function in Excel. In Excel, the MATCH Function can be used as a Worksheet Function to insert a formula in a cell. The  MATCH Function in Microsoft Excel looks for a value in an array and returns the position of that value within the array. For example, if we want to match the value 4 in the range A2:A4 which has values 2,4,6,8 the function will return 2, as 4 is the second item in the range. MATCH Function helps in the following ways:   

1) Frequently used in the INDEX function to find the position of a lookup value (the value being searched) in a row, column, or table.

2) MATCH Function helps to identify the exact matches to your lookup value.

3) The  MATCH Function has the ability to be paired with the VLOOKUP function in Excel, creating a powerful combination that enables users to search for information in a table.

MATCH Formula 

The Excel MATCH Formula is as follows: 

=MATCH(lookup_value, lookup_array,[match_type]). The MATCH Formula consists of the following arguments: 

1) Lookup value (mandatory parameter)- This is the value that needs to be searched. 

2) Lookup array (mandatory parameter)- This is the range of cells which need to be searched. 

3) Match_type(optional argument)- It can be set to 1,0, or –1 to return results as shown below: 
 

How to use MATCH Function in Excel? 

The MATCH Function in Excel is used to find a specific item or value, within a range of cells. It then finds and returns the exact or closest position of a value. This feature is commonly used to identify and mark different cells with the same values present within them.

It is adversely also used to isolate sales that don’t share the same values with other cells. Here is step-by-step guide to understanding how to use the MATCH Function in Excel:

Example

Step 1: Here we are taking an example of employee data with multiple values present in it. Choose a cell within the same spread sheet to create a MATCH Formula.
 

Build your career as a Data Analyst, and learn to prepare tables, charts, sheets and reports with our Data Analysis Training using MS Excel course today! 

Step 2: After choosing the cell, select “Formulas>Lookup & Reference>MATCH. Once you have selected “Formulas”, click on Lookup & Refence, go to the drop-down list and select “MATCH” from it.
 

Step 3: Enter the value whose position and match you wish to find in the Lookup_value box. Add the value in Lookup_array and Match_type box alongside the Lookup_value box. If you use a text value in your formula, it must be enclosed in double quotes (““).
 

Understand how to use Excel workbook settings, register in our Microsoft Excel Expert MO201 course now!    

Step 4: In the Match_type box, if you add “1” as a value, your data must be sorted in ascending order either from A to Z, or from smallest to largest. Similarly, if you, add “0” in the Match_type box, you can to find an exact match or an equal value to the lookup item. If you add “-1” in the Match_type then the data must be sorted in descending order, such as Z to A or greatest to smallest. Now, add search values as shown in the image below:
 

Step 5: The location returned may not match the cell value, enter the value again after choosing the particular cell within the table. Keep entering the correct values again, and repeat the process to locate the right section of the data.
 

MATCH Function in Excel - Key pointers 

Here are a few key points about MATCH Function Excel: 

1) When matching text values, the MATCH Function does not make a difference between uppercase and lowercase letters. 

2) If the MATCH Function is unable to locate a match for the lookup value, a ‘N/A!’ error will be generated. 

3) The function enables approximate and exact matching along with wildcards (* or?) for partial matches.  

4) The location of the first value is returned if the lookup array contains more than one instance of the lookup value. 

Sign up and learn the top ten Excel shortcuts with our Microsoft Excel Masterclass!

Conclusion 

In this blog, we have discussed Excel MATCH Function, how it works in Microsoft Excel and what are the most important points you should keep in mind while using this Function. The Excel MATCH Function described in this blog is a very handy function for data analysis. Now that you know how it works, keep practicing and explore more Excel Formulas and Functions!

Learn in detail about how to use advanced formulas in MS Excel, sign up for our Microsoft Excel Training courses today!