How to use MATCH Function in Excel?

Wondering How to Use the Match Function in Excel? The “MATCH” function is a powerful and versatile tool that helps analysts find the position of a value in a range of cells. The best part about this function is that it can be combined with several other functions, such as INDEX, VLOOKUP, or HLOOKUP, to perform intricate tasks seamlessly. Continue diving into this blog to study the “MATCH” function in-depth and how you can utilise it to ensure seamless working for data professionals. Let’s get the wheels rolling!

Table of Contents

1) What is the “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 the “MATCH” Function in Excel?

"MATCH" is a built-in lookup/reference function in Excel that can be used as a worksheet function to insert a Percentage Formula in Excel cell. The MATCH Function 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) It is 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) It helps to identify the position of a value, either exact or approximate, based on the specified match type.

3) The “MATCH” Function can be paired with the VLOOKUP Function in Excel, creating a powerful combination that enables users to search for the required information in a table.

MATCH Formula 

Like other Excel functions, “MATCH” function also comprises a specific formula for its operation. The Excel “MATCH” Formula is as follows: 

The Excel MATCH Formula is as follows: 

=MATCH(lookup_value, lookup_array,[match_type])Where 

a) Lookup_value is the value that needs to be searched. 

b) Lookup array is the range of cells which need to be searched. 

c) Match_type needs to be set to either 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 analyse a specific item or value within a range of cells. After applying the “MATCH” function, a value's exact or closest position is shown. This feature is commonly used to identify and mark different cells with the same values present within them.

It can also be used to find non-matching values between two datasets, such as sales data. Here is a step-by-step overview of 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.
 

Prepare tables, charts, sheets and reports hassle-free with our Data Analysis Training using MS Excel Course - join today!

Step 2: After choosing the cell, select Formulas >Lookup & Reference> MATCH. Once you have selected “Formulas”, click on Lookup & Reference, then navigate 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 the Lookup_array and Match_type box alongside the Lookup_value box. If you use a text value in your formula, it must be put under double quotes (““).

Perform 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 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, from 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. After choosing a particular cell within the table, enter the value again. Keep entering the correct values again and repeat the process to locate the right section of the data.

MATCH Function in Excel - Key pointers 

Understanding how to effectively utilise Excel functions can significantly enhance your data analysis skills. 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, an #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. 

Become proficient in the top ten Excel shortcuts with our Business Analytics with Excel Course -  register today!

Excel VLOOKUP and MATCH

The VLOOKUP function is one of Excel's most popular functions used to search for a value in the first column of a range and return a value in the same row from a specified column. When combined with the MATCH function, users can enhance the flexibility of their lookups.

Using MATCH for Dynamic Column Index: When you use VLOOKUP, you must specify the column index from which to return the value. This is where MATCH comes in handy. Instead of hardcoding the column index, you can use MATCH to dynamically find the correct column based on the header. This is especially useful in tables where columns might be added or rearranged.

Example:

=VLOOKUP (A2, B1:D10, MATCH ("HeaderName", B1:D1, 0), FALSE)

In this formula, MATCH finds the position of "HeaderName" in the first row (B1

), and VLOOKUP uses that position as the column index.

Excel HLOOKUP and MATCH

HLOOKUP functions similarly to VLOOKUP but searches for a value in the first row and returns a value from a specified row below it. When combined with MATCH, it provides flexibility in row selection.

Using MATCH for Dynamic Row Index: Much like with VLOOKUP, you can use the MATCH function to dynamically find the row index for HLOOKUP. This is beneficial when working with large datasets or tables where the row structure may vary.

Example:

=HLOOKUP (A2, B1:D10, MATCH ("RowName", A1:A10, 0), FALSE)

Here, MATCH identifies the row of "RowName" in column A, allowing HLOOKUP to return the correct value.

Conclusion 

We hope you understand How to Use the Match Function in Excel. 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 exploring more Excel Formulas and Functions!

Excel in advanced MS Excel formulas- sign up for our Microsoft Excel Training today!