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
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
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
