The Excel Match Function

The Excel Match function searches an array (either a row or column) for a specified value and returns the relative position of the value in the array. For example, if we have a list of fruits including bananas, cantaloupes, grapes and watermelon, using match to find grapes would return 3 as grapes is the third item in the list. At first glance, this doesn’t seem to be very useful. But it can be a powerful tool when nested within another function, such as Index. We’ll cover that subject in other articles, but first let’s be sure we understand how to use the match function in Excel.

Match function syntax

Match(lookup value, lookup array, match type)

• Lookup Value is required. Match will search through the lookup array for the lookup value. Lookup value can be either a number, text or a logical value. It can also be a reference to a cell containing a number, text or a logical value.
• Lookup Array is required. Lookup Array is a range of cells, either a row or a column, which will be searched for the lookup value.
• Match Type is optional.
  • 1 or omitted: Match finds the largest value that is less than or equal to lookup value. The values in the lookup array must be in ascending order.
  • 0: Match finds the first value that is exactly equal to lookup value. Lookup array can be in any order.
  • -1: Match finds the smallest value that is greater than or equal to lookup value. The values in the lookup array must be in descending order.

Examples using match function

The sample lookup array might be an employee master file. In the following examples, we will use the Match function to find values in a few of the columns in this table.

In each of the examples below, we use the Match function with a lookup value specified in column C, and return the result in column D. Column E displays the formula contained in Column D. For example, cell E23 contains the formula “=FormulaText(D23)”

Notes to Examples

1. We are searching for the last name “Makit” within the last names column of the data table. In the match formula, “Makit” is the lookup value. The cells in range J3:J7 are the lookup array, and we specified the default match type of 1. The result would be the same if we omitted the match type. Remember, the lookup array must be in ascending order for match type 1 to work properly. Searching for “Makit” in column J, Excel would encounter “Oyl” first in the lookup array. Since “Oyl” is greater than or equal to “Makit”, Excel would end its search and return a #N/A error. The name “Makit” is not in the searched range.
2. The Match formula is identical to example 1, except now we specify a match type of 0. Match type 0 makes no assumptions about the sort order of the lookup array, and will return the first identical match found. In this case, the formula returns 2 as “Makit” is the second name on the list.
3. Example 3 is identical to Example 2, except now we are searching for the last name “Beancounter”. Excel returns the first exact match that it finds, which is in position 4. If there are duplicates within the lookup array, the Match function will always return the first exact match. Note that if we had used match types of 1 or -1, Excel would return a #N/A error. See Example 1 above.
4. In the remaining examples, the lookup array is the Hire Date column (Column M) in our table. The default match type of 1 will work properly since the lookup array is in ascending order. In example 4, we search for a hire date of 6/1/2019 and the Match function correctly returns 3. The lookup value of 6/1/2019 is the third item in the lookup array.
5. In Example 5, we are looking for a hire date that does not appear in the lookup array. Since the lookup array is in ascending order and we are using the default match type, Excel returns the position of the largest value that is less than or equal to the lookup value. That value is 5/22/2019, and is the second value in the list so Match returns 2.
6. Example 6 is identical to Example 5, except now we are looking for an exact match because we specified a match type of 0. Since there is no exact match, the Match function returns a #N/A error.

Summary

You must understand your data and how it is organized to get the most from the Match function. It is important to know which columns are sorted and which are not, and whether or not there might be duplicate values. Proceeding from that understanding, however, the Match function can be combined with other functions to perform powerful lookups far beyond those available with Vlookup or Hlookup.