ExcelCSE.com

Menu
  • Excel 101
  • Excel Functions
  • Excel Formula
  • Contact Us
How to Use Excel > Excel Functions > Excel MATCH Function

Excel MATCH Function

How to Use the Excel MATCH Function
Table of contents :
  • What is the Excel MATCH Function?
  • MATCH Syntax
  • How to Use MATCH Function in Excel
  • MATCH Example

Excel MATCH Function

What is the Excel MATCH Function?

Excel MATCH function returns a relative position of a value in the range. Support for approximate matching, exact matching, and wildcards (*/?) for partial matching.

Excel MATCH function returns the position of value, not the value itself, to get the value use INDEX and MATCH function together.

MATCH Syntax

MATCH(lookup_value, lookup_array, [match_type])

lookup_value, required, the value to match in lookup_array.

lookup_array, required, a range of cells where to match lookup_value.

match_type, [optional], how to match lookup_value in lookup_array.

  • 1 (default), returns the position of the largest value that is less than or equal to lookup_value. The lookup_array must be sorted in ASCENDING order.
  • 0, returns the position of the first value exactly equal to lookup_value. No need for lookup_array to be sorted in any order.
  • -1, return the position of the smallest value that is greater than or equal to lookup_value. The lookup_array must be sorted in DESCENDING order.

Usage Notes:

  • MATCH function matches case-insensitively, no different between uppercase and lowercase letters.
  • MATCH function return #N/A error when no match value in lookup_array and match_type = 0
  • You can use a wildcard character in a lookup_value argument when match_type = 0 and lookup_array is a text string — an asterisk (*) wildcard for any character sequence, and a question mark (?)wildcard for any single character; use a tilde (~) before character to find actual asterisk and question mark.

How to Use MATCH Function in Excel

Exact Match Type

The Question

Where is the position of the month August?

The Formula

=MATCH("august",B3:B14,0)

lookup_value = august (what you are looking for, case insensitive)

lookup_array = B3:B14 (where you are looking for)

match_type = 0 (how you are looking for, exact match, looking for the equal value)

The Result

MATCH Function Excel

Approximate Match Type

Look at the image below, purchase discounts depending on the number of purchasing quantity. Purchase 10 pcs gets a 0.25% discount, purchases 20 pcs gets a 0.5% discount etc.

The Question

How much discount is obtained for purchasing 34 pcs?

The Formula

=MATCH(34,B3:B12,1)

lookup_value = 34

lookup_array = B3:B12

match_type = 1 (approximate match, the lookup_array sorted in ascending order)

The Result

How to Use MATCH Function in Excel

You only got the position of the discount value, to get the discount value use INDEX MATCH Excel function.

The Modified Formula

=INDEX(C3:C12,MATCH(34,B3:B12,1))

The Final Result

Using MATCH Function in Excel

MATCH Example

Another article using or explain about MATCH Function
  • VLOOKUP Multiple Criteria – Solution for Numeric or Non-Numeric Result
  • VLOOKUP MATCH – a Combination for a Solution

Another Lookup & Reference Function

Another article about Lookup & Reference Function
  • Excel CHOOSE Function
  • Excel VLOOKUP Function
  • Excel MATCH Function
  • Excel INDEX Function
Usefull links
  • Microsoft MATCH function documentation
Share
Tweet
Google+
Email
Prev Article
Next Article

Related Articles

Excel AVERAGEA Function
What is the Excel AVERAGEA Function? The excel AVERAGEA function …

Excel AVERAGEA Function

Excel CONVERT Function
What is the Excel CONVERT Function? The excel CONVERT function …

Excel CONVERT Function

How to Use SUMIFS in Excel
What is the Excel SUMIFS Function? The SUMIFS function is …

Excel SUMIFS Function

Excel TEXTJOIN Function
What is the Excel TEXTJOIN Function? The excel TEXTJOIN function …

Excel TEXTJOIN Function

How to Use Excel IFS Function
What is the Excel IFS Function? Excel IFS Function is …

Excel IFS Function

Tags:Excel MATCH Excel MATCH Function Example How to Use Excel MATCH Function How to Use MATCH Function How to Use MATCH Function in Excel How to Use The MATCH Function in Excel Lookup & Reference Function MATCH Function Excel Microsoft Excel MATCH Function MS Excel MATCH Function Using MATCH Function in Excel What Does The MATCH Function Do in Excel What Is MATCH Function in Excel

Categories

  • Excel 101
  • Excel Formula
  • Excel Functions

Recent Posts

  • Get the Original Price from Percentage Increase/Decrease
  • How to Add/Subtract Percentage in Excel
  • How to Extract Day, Month and Year from Date in Excel
  • How to Add Seconds, Minutes and Hours to a Time in Excel
  • How to Add Days, Weeks, Months and Years to a Date in Excel
  • How to Calculate Time Difference in Excel
  • Calculate Number of Days, Weeks, Months and Years Between Two Dates in Excel
  • Flash Fill Excel
  • Paste Special Excel
  • Excel TEXTJOIN Function
  • Excel CONCAT Function
  • Excel CONCATENATE Function
  • Excel REPLACE Function
  • Excel SUBSTITUTE Function
  • Excel REPT Function
  • Excel FIND Function
  • Excel SEARCH Function
  • Excel MID Function
  • Excel RIGHT Function
  • Excel LEFT Function

ExcelCSE.com

The Ultimate Guide How to Use Excel Better
Copyright © 2023 ExcelCSE.com
Contact Us - Privacy Policy - Sitemap | Theme by MyThemeShop.com

Ad Blocker Detected

Our website is made possible by displaying online advertisements to our visitors. Please consider supporting us by disabling your ad blocker.

Refresh