Excel MATCH Function (Table of Contents)
Introduction to MATCH Function in Excel
Excel MATCH function can be used to find out the relative position of a lookup value in a given range of cells. It returns the position occupied by the lookup value (position would be a numeric value/number) in that given range. By the help of Match function, we can fix any cell position and use that along with Index and Vlookup function with proper output
Below is the Syntax for Excel MATCH function:
=MATCH (lookup_value, lookup_array, [match_type])
- lookup_value = The value whose position we wanted to find. Can be a Text, number, logical value or a cell reference.
- lookup_array = An array or range of cells under which we wanted to find out the relative position of lookup_value.
- match_type = An optional argument which defines the logic behind matching. How the match will happen. Takes arguments as -1, 0 or 1.
- If match_type = -1 it means that the MATCH function will find out the smallest value which is greater than or equals to the lookup_value. For this to happen, the lookup_array must be sorted in descending order.
- If match_type = 0 it means that the MATCH function will find out the value which is exactly the same as that of the lookup_value.
- If match_type = +1 it means that the MATCH function will find out the largest value which is lesser than or equals to the lookup_value. For this to happen, the lookup_array must be sorted in ascending order. Default value for the match type is +1.
How to Use MATCH Function in Excel?
MATCH Function in Excel is very simple and easy to use. Let’s understand the Match Function in Excel by some examples given below.
Suppose you have a dataset of employees as below:
Suppose you would like to find out the position of an employee with the name ‘Lalit’. You can do it using Match function with the following steps.
- Set up the lookup_value in cell C13 as ‘Lalit’.
- In cell C15, input the following formula:
- Click Enter once done with the formula.
You can see the output as 5 in cell C15. Which is the relative position of an employee with the name ‘Lalit’. Please note that the lookup_array is starting from B2. If we consider it starting from B1, the position of the employee with the name ‘Lalit’ will be 6.
Also, note that the employee names are not sorted and hence the match_type which suits this data is 0 (zero) and hence used.
Following wildcard characters can be used under MATCH function.
- Question Mark (?) – to replace a single character.
- Asterisk (*) – to replace a series of characters (any number of characters after * sign).
MATCH with wildcard characters can be used in scenario’s where you wanted to check any specific part of the string.
See the examples below:
Suppose you wanted to search the position of an employee with name begins with ‘Jo’ using MATCH function.
You can do it in two ways –
- Enter the formula: =MATCH(“Jo*”,B2:B11,0) in cell B13.
After pressing Enter, we will get a result as follows.
- You can input lookup_value (‘Jo’) in cell B13 in order to make our formula more general and then concatenate it with the asterisk (*). Put the below formula in cell B15 : =MATCH(B13&”*”,B2:B11, 0)
This formula will give exactly the same output. The only difference it has is this one is more generalized than the previous one. If you change the lookup_value in cell B13, it will automatically change the output in cell B15. See the output below:
Please note that the MATCH function only takes 0 (zero) as a match_type while working with wildcards. If you want to see the relative position based on a single character string, then use the question mark (?) as an operator.
- Input following formula in cell B13: =MATCH(“Ma?tha”,B2:B11,0)
- Once done, press Enter to see the output.
This will match the name ‘Martha’ in an array and gives its relative position.
Let’s use the MATCH function in combination with SUM and INDEX function
Suppose you have data of traveling expenses (in USD) of 10 executives for the past 4 months (Nov-2018, Dec-2018, Jan-2019, and Feb-2019). These expenses are to be paid by the company on the first date of the next month. You wanted to calculate the total amount we credited in the employee’s account in any particular month. You can do that with a combination of MATCH SUM and INDEX function. We will go step by step.
See the data below:
- In cell B13, mention the lookup_value (Month for which we wanted to sum up the data). I choose Dec-18.
- Put the following formula in cell B15: =SUM(INDEX(B2:E11,0,MATCH(B13,B1:E1,0)))
- Press Enter, once done to see the output as below:
INDEX and MATCH functions are at the core of this formula.
- The MATCH function is used to find out the column to which the value in cell B13 belong.
- The output of MATCH function is used as a column input for INDEX function which becomes INDEX(B2:E11,0,2).
- As the row_num is set to 0 (zero) in INDEX function, it returns all the values in each row for matching column as an input to SUM function.
- SUM function then returns sum of all observations in the column as $ 20,254.
There are several other formulas (especially lookup formulas) in which MATCH function can be used in combination. However, it is often used with INDEX function.
Things to Remember
- MATCH is not case sensitive. Means it is not able to distinguish between lower case and upper case if a text string is provided as an Ex. ‘Lalit’ & ‘lalit’ are the same for MATCH function.
- If no match found for the lookup_value in the lookup_array, this function returns ‘#N/A’.
- This function gives a relative position of the lookup_value as output and not the lookup_value itself.
- If the lookup_array has several occurrences of lookup_value, the position of value which is first among those all will be given as an output.
- Wildcard characters (* & ?) can only be used in MATCH function when the match_type is set to 0 (Zero) and lookup_value is text.
- If match_type is not provided, then it will be considered as +1 by default.
This is a guide to MATCH Function in excel. Here we discuss how to use MATCH Function in excel with practical examples and downloadable excel template. You can also go through our other suggested articles –