We may not have the course you’re looking for. If you enquire or give us a call on +55 8000201623 and speak to our training experts, we may still be able to help with your training requirements.
Training Outcomes Within Your Budget!
We ensure quality, budget-alignment, and timely delivery by our expert instructors.
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 percentage 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:
Match_type |
Behaviour |
1 |
The position of the closest match below the lookup value will be returned if the method is unable to identify an exact match. (The lookup array must be in ascending order if this option is used.) |
0 |
The function will return an error if it is unable to identify an exact match. (If this option is used, it is not necessary to enter the lookup array). |
-1 |
The position of the closest match above the lookup value will be returned if the function is unable to identify an exact match. (The lookup array must be in descending order if this option is used). |
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!
Frequently Asked Questions
To troubleshoot and debug formulas containing the MATCH Function in Excel, ensure correct syntax, verify cell references, and check for errors like incorrect data types. Review MATCH Function parameters, validate lookup arrays, and use Excel's formula auditing tools for diagnosis and resolution.
The Microsoft Excel MATCH Function enhances worksheet speed and performance by efficiently locating and retrieving data based on specified criteria. By quickly identifying the position of a value within a range, MATCH facilitates faster calculations and data retrieval, improving overall worksheet responsiveness.
The Knowledge Academy takes global learning to new heights, offering over 30,000 online courses across 490+ locations in 220 countries. This expansive reach ensures accessibility and convenience for learners worldwide.
Alongside our diverse Online Course Catalogue, encompassing 17 major categories, we go the extra mile by providing a plethora of free educational Online Resources like News updates, Blogs, videos, webinars, and interview questions. Tailoring learning experiences further, professionals can maximise value with customisable Course Bundles of TKA.
The Knowledge Academy’s Knowledge Pass, a prepaid voucher, adds another layer of flexibility, allowing course bookings over a 12-month period. Join us on a journey where education knows no bounds.
The Knowledge Academy offers various Microsoft Excel Training & Certification Courses, including Microsoft Excel Masterclass, Business Analytics with Excel and Excel Training with Gantt Charts. These courses cater to different skill levels, providing comprehensive insights into How to Create a Project Plan in Excel.
Our Office Applications blogs cover a range of topics related to Microsoft Excel, offering valuable resources, best practices, and industry insights. Whether you are a beginner or looking to advance your Excel skills, The Knowledge Academy's diverse courses and informative blogs have you covered.
Upcoming Office Applications Resources Batches & Dates
Date
Fri 20th Sep 2024
Fri 15th Nov 2024
Fri 20th Dec 2024
Fri 10th Jan 2025
Fri 14th Feb 2025
Fri 11th Apr 2025
Fri 23rd May 2025
Fri 8th Aug 2025
Fri 26th Sep 2025
Fri 21st Nov 2025