MATCH Function

  • Applies to: Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel for the web, Excel 2019, Excel 2016, Excel 2019 for Mac, Excel 2013, Excel 2010, Excel 2007, Excel 2016 for Mac, Excel for Mac 2011, Excel Starter 2010.

Tip: Try using the new XMATCH function, an improved version of MATCH that works in any direction and returns exact matches by default. In effect, this makes it simpler easier and more user-friendly relative to its predecessor.

Firstly, the MATCH function searches for a distinct item in a range of cells. After this, it then returns the relative position of that item in the range. For example, if the range A1:A3 contains the values 5, 25, and 38, then the formula =MATCH(25,A1:A3,0) returns the number 2. This is because 25 is the second item in the range.

Tip: Above all, you are best advised to use MATCH rather than one of the LOOKUP functions anytime you require the position of an item in a range instead of the item itself. For example, you may use the MATCH function to supply a value for the row_num argument of the INDEX function.

Syntax of the MATCH Function

MATCH(lookup_value, lookup_array, [match_type])

The MATCH function syntax contains the following arguments:

  • lookup_value – Required. The specific value that you seek to match in lookup_array. For instance, when you look up someone’s number in a telephone book, you are employing the person’s name as the lookup value, but the telephone number is your target value.

    The lookup_value argument can comprise a value (number, text, or logical value) or a cell reference to a number, text, or logical value.
  • lookup_array – Required. The range of cells being scanned.
  • match_type – Optional. The number -1, 0, or 1. The match_type argument defines how Excel matches lookup_value with values in lookup_array. The base value for this argument is 1.

How to use the MATCH function in Excel
© Exceljet, 2020.

The following table outlines how the function captures values according to the setting of the match_type argument.

Table showing how the MATCH Function captures values based on the match_type argument setting

Match_typeBehaviour
1 or omittedMATCH detects the largest value that is less than or equal to lookup_value. For such cases, the values in the lookup_array argument should be arranged in ascending order, for example: …-2, -1, 0, 1, 2, …, A-Z, FALSE, TRUE.
0MATCH spots the first value that is precisely equal to lookup_value. In this case, the values in the lookup_array argument can be ordered randomly.
-1MATCH obtains the smallest value that is greater than or equal to lookup_value. In this instance, the values in the lookup_array argument must be placed in descending order. For example: TRUE, FALSE, Z-A, …2, 1, 0, -1, -2, …, and so on.

Key Conditions of the MATCH Function

  • MATCH returns the position of the matched value inside lookup_array, rather than the actual value . For example, MATCH(“b”,{“a”,”b”,”c”},0) returns 2, which is the relative position of “b” among the array {“a”,”b”,”c”}.
  • MATCH constantly treats uppercase and lowercase letters as the same value when matching text values.
  • If MATCH fails to locate a match, it returns the #N/A error value.
  • If match_type is 0 and lookup_value is a text string, you can deploy the wildcard characters — the question mark (?) and asterisk (*) — in the lookup_value argument. A question mark reflects any single character; an asterisk matches any sequence of characters. If you want to locate an actual question mark or asterisk, input a tilde (~) before the character.

Example

Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to present results, choose them, press F2, and then press Enter. If you must, you can modify the column widths to view all the data.

ProductCount
Bananas25
Oranges38
Apples40
Pears41
FormulaDescriptionResult
=MATCH(39,B2:B5,1)Because there is not an exact match, the position of the next lowest value (38) in the range B2:B5 is returned.2
=MATCH(41,B2:B5,0)The position of the value 41 in the range B2:B5.4
=MATCH(40,B2:B5,-1)Returns an error because the values in the range B2:B5 are not in descending order.#N/A

See Also

VLOOKUP function

INDEX function

Leave a Reply

%d bloggers like this: