What is MATCH function in Excel?
The MATCH function is used to search for a value in a range in order to retrieve the position of a cell that matches the search term.
Assuming you have a list of names in a column, this function can look through the names in that column and retrieve the position of a particular name you specified.
This function is one of those that are less useful on their own unless they are used in conjunction with another function. It is commonly used together with the INDEX function to perform lookups.
MATCH function syntax
=MATCH(lookup_value, lookup_array, [match_type])
This function goes with three arguments: the lookup_value, the lookup_array, and the [match_type].
Before I walk you through an example, let me quickly explain these arguments. It’ll maximize your understanding when dealing with real examples.
- lookup_value: This argument represents the value you are looking for. When Excel spot this value, it’ll return a number that represents its position. This value could either be text or numbers.
- lookup_array: This argument represents the range you want to look through. When specifying the lookup array, the range must be one-dimensional. This means that you can only search through a single column or a single row. For instance, you can search through A1:A10 or A1:G1. But you can’t search a grid of cells (like A1:G10 which include several rows and columns).
- [match_type]: This argument is used to specify how the match is determined. A match_type of zero will retrieve the position of the first item that matches exactly. In most cases, the 0 match_type is used. So, I highly recommend you to always stick to it unless you know what you are doing. A match_type of 1 will retrieve the position of the largest value that’s equal to or less than the searched value. A match_type of -1 will give you the position of the smallest value that’s equal to or greater than the searched value.
Now let’s see how the MATCH function works in the following example.
MATCH() function Example
This is a table that contains some customer order information. And there are about 10 records in this table.
To illustrate the MATCH function, let’s write a formula to retrieve the position of a customer named David in the customer list.
How to write the MATCH function
First of all, bring your equal sign followed by the word match.
And let me show you this technique. When you begin typing the function with the first letter M, Excel will show you all the functions that begin with this letter.
Fortunately, the first suggested function is the MATCH function. So, there’s no need to type everything. Just press the tab key and Excel will insert the function for you.
Match function first argument (lookup_value)
Now specify the first argument of this function, which is the lookup value.
Since we want to retrieve the position of David in the list, the lookup value will be “David”.
If the lookup value is the text as in David, always make sure to surround it with double-quotes. But in the case of numbers, there’s no need for any quotes.
See screenshot:
Also, you can use the content in another cell as the lookup value by reference to that cell. For instance, if you use B2 as your lookup value, the content in cell B2 will be used as the lookup value.
This also means that whenever you change the content in cell B2, you changed the lookup value too, and therefore the result of the match function also will change.
See screenshot:
Match function second argument (lookup_array)
Now that we specify the first argument, let’s move on to the second argument which is the lookup_array.
This argument specifies the range in which Excel will look through for our lookup value (which is David).
I mentioned before that when writing this function, the lookup array has to be one-dimensional. This means that you can only search a single row or a single column. But you can’t search through a grid of cells with multiple columns and rows.
Since we are looking for the name David, we’ll have to search in cells A5 through A14. Thus, the lookup_array will be A5:A14.
See screenshot below:
Match function third Argument ([match-type])
Let’s move on to the next argument – which is the [match_type].
Here, there are three match types.
A match type of 1 (one) means less than: which also means that excel will retrieve the position of the largest value that’s equal to or less than the lookup value.
A match type of -1 means greater than: this match type will give you the position of the smallest value that’s equal to or greater than the lookup value.
These two match types make sense when you are searching through numbers. It becomes handy when you don’t know the exact value you are looking for. This means that Excel will return a result based on the closest value to the search term.
However, when dealing with text, always go with a match type of 0.
A match_type of zero means Exact match: this match type will retrieve the position of the first item that matches exactly.
In most cases, the 0 match_type is used. So, I highly recommend you to always stick to it unless you know what you are doing.
Now that all the arguments are exhausted, close the parenthesis and press enter to run the formula.
Result
The result is 5 which means that the position of David is position number 5. In other words, David is the fifth name in the list.
However, if there’s no match – in other words, if David was not in the list, the function will return a #N/A error which means that what you are looking for is not in the list.
This is exactly how the MATCH function works.
Things you should know about MATCH function
- The MATCH function is not case sensitive. This means that DAVID is the same as David (or David).
- The MATCH function only works with one-dimensional arrays (range). This means that you can only look through a range of cells in the same column or row. If your lookup_array has a range with a grid of cells that include several rows and columns, then the function will return #N/A.
- The MATCH function returns #N/A if no match is found in case of a match type of zero. But for a match type of 1 and -1, the function may return an approximate match.
- If you are using a MATCH type of 1 or -1, always ensure that the data in the range is sorted in descending order.
- When a MATCH type is zero, and the search term is text, you can use wildcard characters to perform a partial search.
The MATCH function doesn’t seem so useful by itself. But when combined with other functions, it becomes very helpful.