Excel MATCH Function (Table of contents)
MATCH in Excel
In Microsoft Excel, there are so many distinct lookups/reference functions that can help you find a certain value in a range of cells, and MATCH is one of them. Basically, it identifies a relative position or location of any item in a range of cells. However, the MATCH can do much more than its pure essence.
MATCH function is used to search the location of a lookup value in a table or a row, column. MATCH finds approximate and exact matches and wildcards (* ?) for limited matches. Most of the time, the INDEX function is 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 respect location of that value.
The Formula for the MATCH function is as follows:
- 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 which is equal 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
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:
=MATCH(E2,B3:B10,0)
The MATCH Function returns the Exact match as 4.
2. Approximate Match
MATCH will perform an approximate match on values sorted A-Z when 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:
The MATCH in Excel returns an approximate match as 7.
3. Wildcard Match
MATCH function can perform a match using wildcards when match type is set to zero. In the below-given example, the formula in E3 is:
The MATCH Function returns the result of wildcards as “pq”.
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 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 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 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:
The MATCH Function returns the position of MARS as 4.
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: For checking a cell for one of many things and gives back a custom result for the first match found, 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 above is an array formula, it should be entered using Control + Shift + Enter keys.
How does this formula work?
This formula uses two name ranges: E5:E8 is named as “things”, and F5:F8 is named as “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)))
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:
Also, some users experience the following common problem with the Match Function:
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 has been a guide to MATCH in Excel. Here we discuss the MATCH Formula and how to use the MATCH Function in excel with 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 –
