What is a Match Function?

The match function in MS Excel is one of the useful built-in lookup functions that work like Vlookup, Hlookup, and the Index function to return the position of exact or similar matches of the lookup value found in an array or a table.

Syntax of the Function in Excel

Match(< lookup value > , < lookup array > , < match type >)

Where

1. < lookup value > is the value that we search for
2. < lookup array > is the range of cells in which we are looking for the “lookup value”
3. < match type > is the type of comparison we want to do and find a match. It could be one of the following:

• 1 : less than–It finds the largest value in the lookup array that is less than or equal to the lookup value. The array should be in ascending order for this to work as expected.
• 0 : exact match–It looks for an exact match of the lookup value in the lookup array regardless of the order in which the elements are.
• –1 : greater than–It finds the smallest value in the lookup array that is greater than or equal to the lookup value. The array should be in descending order for this to work as expected.

Examples Using the Match Function Manually in Excel

Here I’ll show you where the Match function is used with examples of all three match types.

The formula / function with the parameter values is available in the Formula bar.

Less than match type – Position 3 in the lookup array is returned.

Equal to match type – Position 3 in the lookup array is returned.

Greater than match type – position 6 in the lookup array (col D as seen in the formula bar) is returned.

Using the Match Function in VBA

In VBA, the worksheetfunction object can help us use the Match function. Check out that linked article to get more information about the worksheetfunction object.

Examples: Match Function with Different Match Type Values

Match Type Value 1

Say you want a program to purchase a food item within a budget. We use match type 1 here. So, for this to work as desired, the values of the array, the range used as a parameter for the function, should be in ascending order.

Sub match_func_demo()

    Dim snacks() As Variant
    Dim price() As Variant
    Dim budget, rec_item, rec_pos
    
    price = Array("50", "55", "60", "65", "70", "70", "71", "72", "94", "110", "120", "125")
    snacks = Array("Ice cream", "Sweet", "Candy", "Cake", "Pulao", "Roti", "Idly", "starters", "Biscuits", "Chilli popcorn", "Soup", "Papad")
    
    budget = InputBox("Please enter your budget")
    
    rec_pos = Application.WorksheetFunction.Match(budget, price, 1)
    
    Debug.Print "You may buy " & snacks(rec_pos - 1) & " which costs $" & price(rec_pos - 1)

End Sub

If we enter 55 as the input during runtime, the program suggests the second item.

If we enter 62 as the budget, the item with closest value, which is less than the budget, is displayed (60 in this case).

If the budget is 73, the item with the closest low value i.e. “starters” in this program is suggested.

Here is the output in the image below for the above three input budget values:

Match Type Value 0 

In the same program (used in match type 1) we are shuffling and changing some array values of the “Price” array. As we know, this match type 0, returns a position only if an exact match is found.

price = Array("390", "55", "160", "65", "70", "70", "71", "72", "94", "10", "120", "125")
snacks = Array("Ice cream", "Gulab Jamun", "Candy", "Cake", "Rasgulla", "Roti", "Idly", "Veg clear soup", "Cookies", "Chilli popcorn", "Soup", "Masala Papad")

If the budget is 55, we are suggested to buy the second item.

In case the budget is 390, we are suggested the first item in the array “snacks.”

If the budget exactly matches any value in the array, we are suggested the matching value. 

If not, we encounter an error as in the image below. This error can be handled using an “On error goto“ statement. 

Hence, the match type “0” should only be used when we can expect exact matches.

Conclusion

The match function can be of use when exact and nearest values need to be chosen alternatively depending on the situation. The match type parameter is optional, but tells whether we need an exact match or lower value or a higher value that is close to the lookup value.

This function works not only for numbers but also for strings or expressions. For expressions, alphabetical order is considered to produce a favorable result.