EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home Excel Excel Resources Lookup & Reference Functions in Excel MATCH in Excel
Secondary Sidebar
Excel Functions
  • Lookup and Reference Functions in Excel
    • VLOOKUP Function in EXCEL
    • VLOOKUP True
    • VLOOKUP Error
    • How to Match Data in Excel
    • Excel Match Function
    • Excel Lookup Function
    • ROWS Function in Excel
    • Excel INDEX Function
    • VLOOKUP Table Array
    • Excel OFFSET Formula
    • VLOOKUP For Text
    • IF VLOOKUP Formula in Excel
    • Mixed Reference in Excel
    • CHOOSE Formula in Excel
    • Excel COLUMN to Number
    • Excel Alternatives to VLOOKUP
    • HLOOKUP Examples
    • Excel VLOOKUP From Another Sheet
    • VLOOKUP with Sum
    • Fixing VLOOKUP Errors
    • Excel ROW Function
    • HYPERLINK in Excel
    • Address Excel Function
    • Excel COLUMNS Function
    • Excel REPLACE Function
    • OFFSET Excel Function
    • Excel GETPIVOTDATA Function
    • MATCH Function in Excel
    • VLOOKUP Function in Excel
    • HLOOKUP Function in Excel
    • LOOKUP in Excel
    • CHOOSE Function in Excel
    • TRANSPOSE in Excel
    • COLUMN Function in Excel
    • INDIRECT Function in Excel
  • Excel Functions (12+)
  • Excel Tools (114+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (14+)
  • Maths Function in Excel (47+)
  • TEXT and String Functions in Excel (26+)
  • Date and Time Function in Excel (24+)
  • Statistical Functions in Excel (59+)
  • Information Functions in Excel (4+)
  • Excel Charts (56+)
  • Excel Tips (222+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (22+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • Online EXCEL COURSE
  • Online EXCEL ADVANCED Training

MATCH in Excel

By Madhuri ThakurMadhuri Thakur

MATCH in Excel

Excel MATCH Function (Table of contents)

  • MATCH in Excel
  • MATCH Formula in Excel
  • Types of MATCH Function in Excel
  • How to Use MATCH Function in Excel?

MATCH in Excel

In Microsoft Excel, many distinct lookups/reference functions can help you find a certain value in a range of cells, and MATCH is one of them. Basically, it identifies the relative position or location of any item in a range of cells. However, MATCH can do much more than its pure essence.

The MATCH function searches the location of a lookup value in a table or a row column. MATCH finds approximate and exact matches and wildcards (* ?) for limited matches. The INDEX function is mostly integrated with a MATCH function to retrieve the value at the location returned by MATCH.

MATCH Formula in Excel

The MATCH Function checks for a particular value in a range of cells and returns the respective location of that value.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

The Formula for the MATCH function is as follows:

All in One Excel VBA Bundle(120+ Courses, 30+ Projects)
Basic ExcelAdvanced ExcelVBA & MacrosExcel Dashboards
Pivot TablesStatistical FunctionsFinancial FunctionsCharts & Graphs
Price
View Courses
120+ Online Courses | 30+ Projects | 500+ Hours | Verifiable Certificates | Lifetime Access
4.9 (64,768 ratings)

MATCH Formula

  • Lookup_value (required) – the value you are searching for. It can be either numeric, text or logical value as well as a cell reference.
  • Lookup_array (required) – search from the range of cells.
  • Match_type (optional) – explains the match type. It can be any one of these values: 1, 0, -1. The match_type argument, when setting to 0, returns the exact match, while the other two types of values allow for an approximate match.

1 or omitted (default) – searches for the largest value in the lookup array, which is less than or equal to the lookup value. Requires sorting of the lookup array in ascending order, from smallest to largest or from A to Z.

0 – finds the first value in the array that is absolutely equal to the lookup value. Sorting is not required.

-1 – finds the smallest value in the array that is equal to or greater than the lookup value. The lookup array needs to be sorted in descending order, from largest to smallest or from Z to A.

MATCH in Excel – Usage

Use the MATCH function to get the respective location of an item in an array. Match has different types of matching modes, which makes it more versatile than the lookup functions. Usually, when the MATCH function is combined together with INDEX, it can retrieve the value of the latched position.

Match type information

  • If 1 is match_type, MATCH finds the largest value equal to or less than lookup_value. The lookup_array must be sorted out in ascending order.
  • If 0 is match_type, MATCH finds the first value precisely equal to lookup_value. lookup_array does not require any sorting.
  • If -1 is match_type, MATCH gives the smallest value, which is equal or greater than

lookup_value. The lookup_array must be sorted out in descending order.

  • If match_type is omitted, it is assumed as 1.
Note: All-match types usually find an exact match.

Types of MATCH Function in Excel

Below are the different types of MATCH Function in Excel:

1. Exact Match

MATCH function performs an exact match when the match type is set to zero. In the below-given example, the formula in E3 is:

You can download this MATCH Function Excel Template here – MATCH Function Excel Template

=MATCH(E2,B3:B10,0)

Exact

The MATCH Function returns the Exact match as 4.

MATCH Exact match 1-1

2. Approximate Match

MATCH will perform an approximate match on values sorted A-Z when the match type is set to 1, finding the largest value less than or equal to the lookup value. In the below-given example, the formula in E3 is:

Approximate

The MATCH in Excel returns an approximate match as 7.

MATCH Approximate match -1

3. Wildcard Match

MATCH function can perform a match using wildcards when the match type is set to zero. In the below-given example, the formula in E3 is:

Wildcard

The MATCH function returns the result of wildcards as “pq”.

MATCH wildcard match -1

Notes:

  • A MATCH Function is not case-sensitive.
  • Match returns the #N/A error if there is no match is found.
  • The argument lookup_array must be in descending order: True, False, Z-A,…9,8,7,6,5,4,3,…, and so on.
  • The wildcard characters like an asterisk and question mark can be found in lookup_value if match_type is equal to 0 and lookup_value is in text format,
  • Lookup_value can have the wildcard characters like an asterisk and a question mark if match_type happens to be 0 and lookup_value happens to be text. An asterisk (*) matches any type of sequence of characters; Any single character is matched by a question (?) mark.

How to Use MATCH Function in Excel?

MATCH Function in Excel is very simple and easy to use. Let understand the working of the Match function in excel with some examples.

Example #1

To better understand the MATCH function, let’s make a simple formula based on this data: Planet names in column A with their positions. To find out where a specific planet (say, Mars) list among others, use this simple formula:

MATCH Function Example 1-1

The MATCH function returns the position of MARS as 4.

MATCH Function Example 1-2

As you see in the screenshot above, the planet names are entered in an arbitrary order, and therefore we set the match_type argument to 0 (exact match) because only this match type does not require sorting values in the lookup array. Technically, the Match formula returns the relative position of Mars in the range being search.

Example #2 – If a cell is containing one of many things

Generic formula : {=INDEX(results,MATCH(TRUE,ISNUMBER(SEARCH(things,A1)),0))}

Explanation: To check a cell for one of many things and give back a custom result for the first match found, the INDEX / MATCH function formed on the SEARCH function can be used.

In the example shown below, the formula in cell C5 is:

{=INDEX(results,MATCH(TRUE,ISNUMBER(SEARCH(things,B5)),0))}

Since the above is an array formula, it should be entered using Control + Shift + Enter keys.

MATCH Function Example 2

How does this formula work?

This formula uses two name ranges: E5:E8 is named “things”, and F5:F8 is named “results”. Make sure you use the name ranges with the same names (depending on the data). If you don’t want to use name ranges, use like absolute references instead.

The main part of this formula is the below snippet:

ISNUMBER(SEARCH(things, B5)

This is based on another formula that checks a cell for a single substring. If the cell has the substring, the formula gives TRUE. If not, the formula gives FALSE.

Example #3 – Lookup using the lowest value

Generic formula =INDEX(range,MATCH(MIN(vals),vals,0))

Explanation: To lookup information associated with the lowest value in a table, you can use a formula depending on MATCH, INDEX, and MIN functions.

In the example shown below, a formula is used to find the name of the contractor who has the lowest bid. The formula in F6 is:

=INDEX(B5:B9,MATCH(MIN(C5:C9),C5:C9,0)))

MATCH Function Example 3

How does this formula work?

Working from the inside out, the MIN function is generally used to find the lowest bid in the range C5:C9:

The result, 99500, is fed into the MATCH function as the lookup value:

Match then gives back the position of this value in the range, 4, which goes into INDEX as the row number along with B5:B9 as the array:

=INDEX(B5:B9, 4) // returns Cymbal

The INDEX function then gives back the value at that position: Cymbal.

Match Function Errors

If you get an error from the Match function, this is likely to be the #N/A error:

Error 1

Also, some users experience the following common problem with the Match Function:

Error 2

Conclusion

MATCH Function is a popular function that most of us use without digging deep into its mechanic. Even after its obtuse purpose (to return back the position of a value within an array), the function clearly has a number of viable uses within Excel.

Recommended Articles

This is a guide to MATCH in Excel. Here we have discussed the MATCH Formula and how to use the MATCH Function in Excel with the INDEX function, along with practical MATCH Function examples and downloadable excel templates. You may also have a look at these other lookup and reference functions in excel –

  1. INDEX MATCH Function in Excel
  2. Excel Match Multiple Criteria
  3. Matching Columns in Excel
  4. VBA Match
Popular Course in this category
Excel Training (23 Courses, 9+ Projects)
  23 Online Courses |  9 Hands-on Projects |  110+ Hours |  Verifiable Certificate of Completion
4.9
Price

View Course

Related Courses

Excel Advanced Training (16 Courses, 23+ Projects)4.9
1 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download MATCH Function Excel Template

EDUCBA

Download MATCH Function Excel Template

EDUCBA

डाउनलोड MATCH Function Excel Template

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more