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.
It is one of those functions that are less useful on their own unless they are used in conjunction with other functions. It is commonly used 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 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 function works in the following example.
MATCH() function Example
Download the example file:
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.
Pro tip: 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 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.
Also, you can use the content in another cell as the lookup value by referencing 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.
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 the MATCH 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 cell 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 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.
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.
Dealing with the #N/A message
This is Excel’s way of telling you that the value you are searching through does not exist.
When you encounter this message after writing your formula, there are two ways to solve it.
- First, if you mistyped the lookup value, making the correction should resolve this. So make sure you check the spelling of the lookup value and also ensure that there are no leading and trailing spaces in the cell.
- The other solution is to use the IFERROR function to hide the message or display a different message. To use the IFERROR function, use the match function as its first argument and then your error message as the second argument. The formula should, therefore, look like this: =IFERROR(MATCH(B2, A5:A14, 0), “-“).
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 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.
Useful Links about the MATCH function
The articles below are examples of how the MATCH function is used with another function (INDEX):