How To Use The MATCH Function in Excel (Examples & Free Exercise File)

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

matchIf you have ever needed the position of an item in a range rather than the actual item itself, you could use the ‘MATCH’ function.

This can be very useful, for instance, when you need the ‘row_num’ parameter for the ‘INDEX’ function.

‘MATCH’ is designed to do just that kind of thing so you won’t be required to count manually.

*This tutorial is for Excel 2019/Microsoft 365 (for Windows). Got a different version? No problem, you can still follow the exact same steps.

“Before we begin to dive into learning how to use ‘MATCH’, let’s review the syntax!”
Kasper Langmann, Co-founder of Spreadsheeto

The syntax of MATCH:

MATCH(lookup_value, lookup_array, [match_type])

The function has three parameters or arguments, the last one being optional (as indicated by the brackets).

1: The first argument, ‘lookup_value’, is simply the value of the item we are looking for the position of.

2: The ‘lookup_array’ is the range in which we want to search for the item in.

3: ‘match_type’ is limited to -1, 0, or 1, depending on the type of ‘MATCH’ we desire.

  • If we want an exact ‘MATCH’, we use “0”.
  • A “-1” will search for the smallest value that is greater than or equal to our ‘lookup_value’.
  • “1” will search for the largest value less than or equal to our ‘lookup_value’.

*Note that if we omit ‘match_type’, a ‘match_type’ of 1 is the default.

Additional thoughts on ‘match_type’

Often, we will want to set the ‘match_type’ to 0 in order to find an exact match.

However, there are scenarios in which we may not know if our ‘lookup_value’ actually exists and we would like to find the closest value possible.

A very important note about the inexact ‘match_type’ parameter ‘1’ is that the values in the ‘lookup_array’ must be sorted in ascending order. Conversely, if we choose the ‘match_type’ parameter of -1, the values in the ‘lookup_array’ must be sorted in descending order. ”
Kasper Langmann, Co-founder of Spreadsheeto

Again, let’s recall that ‘MATCH’ does not output the value of our lookup but the actual position within the ‘lookup_array’.

For instance, ‘=MATCH(“red”,{“red”, “green”, “blue”},0)’ returns an output of 1 since that is the position of our ‘lookup_value’, “red”, in our ‘lookup_array’.

Also note that if ‘MATCH’ cannot find a match to the ‘lookup_value’, it will return the ‘#N/A’ error.

Quick Tip On Managing Errors

errorWe can remove the ‘#N/A’ error if our ‘MATCH’ function does not find a match by using another Excel function: ‘IFERROR’.

This is a conditional function that allows us to replace the error itself with a value of our choosing such as  “ERROR”, “unmatched”, or even a blank(“”).

All we need to do is precede our ‘MATCH’ function with the ‘IFERROR’ function and decide what value we would like to place in the destination cell to replace the ‘#N/A’. Here’s the syntax:

Syntax: IFERROR(value, value_if_error)

It’s that simple.

Referring back to our ‘MATCH’ example above, we can now write the formula as ‘=IFERROR(MATCH(“red”,{“red”, “green”, “blue”},0),””) if we want to replace any ‘#N/A’ errors with a blank cell.

Therefore, if we replaced our  ‘lookup_value’ with “purple” , our result would be a blank rather than ‘#N/A’ since there is no such value in the ‘lookup_array’.

A Practical Example On How To Use Exact Match

Ok, enough of the theoretical discussion.

Let’s get to work and see ‘MATCH’ in action!

We will be looking at a table of first quarter sales totals for a handful of sales persons.

Follow along by downloading our free sample file right below!

Click to Download This Tutorial’s FREE Sample File

It’s a small sample, but just imagine scaling the power of ‘MATCH’ in this simple example to a much larger data set.

Dataset

The first thing we will try is an exact ‘MATCH’ for a salesperson name, “James O’Nesti”.

Be careful to recall that ‘MATCH’ returns the relative position in the ‘lookup_array’ of our ‘lookup_value’.

So when we select a column as our ‘lookup_array’, the value that ‘MATCH’ returns will not necessarily match the number of the row in the worksheet itself.

Just because “James O’Nesti” is on row 5 in our example worksheet, if our ‘lookup_array’ is actually the range A5:A8, the result of our ‘MATCH’ would be ‘1’ since the relative position of our lookup value is the third value in the range.

How to use inexact match with ‘match_type’ ‘-1’

It is very common to use the ‘match_type’ of 0 for an exact match, but let’s take a look at a scenario in which we might use the inexact ‘MATCH’ function parameter values of -1 and 1.

Let’s say we want to find all sales totals greater than $10,000 for the month of January.

In this case, we will want to use the inexact ‘MATCH’ function parameter of -1 since we know this will find the smallest value greater than or equal to our ‘lookup_value’.

Since $10,000 is the minimum value we are setting as our cut-off point, the ‘match_type’ value is the perfect choice just in case there is no value in the ‘lookup_array’ that is exactly $10,000.

“Remember, in order for the inexact match parameter of -1 to work, we have to sort our ‘lookup_array’ values in descending order (or largest to smallest). In order to do this, we simply add a filter to our table headers and sort accordingly.”
Kasper Langmann, Co-founder of Spreadsheeto

First, highlight the row with table headers (row 4) by clicking on the row number itself to the left of cell A4.

Then go to the ‘Editing’ section of the ‘Home’ tab and click on ‘Sort & Filter’.

Select ‘Filter’ in the dropdown that appears and you are set.

*Alternatively (and highly recommended for the sake of efficiency), with your row selected simply press ‘Ctrl + Shift + L’, and this will enable filtering on the selected cells.

Insert a filter in Excel

Once we have done this, our table should now have filters active for the column headers.

Active filters in a worksheet

Now we can select the filter for ‘Jan’ and select ‘Sort Largest to Smallest’ and click ‘OK’ to put the values in descending order.

Sort largest to smallest in your filter

Now we have our table sorted the way it needs to be sorted in order for our ‘MATCH’ function to work the way we need it to.

Sorted spreadsheet

We could type ‘=MATCH(10000,B5:B8,-1)’ to get our result.

However, we will create a table that allows us to input the ‘lookup_value’ into a cell in order for us to use a cell reference instead of the literal value.

Please note:

For our literal lookup value for $10,000, we input ‘10000’ with no quotes since it is a number and not a string.

Also, we did not separate thousands with a comma because Excel would assume that this is the separation between the first and second arguments of the function.

The table we have created in A17:E19 shows the details of our ‘MATCH’ look up for the smallest value in the ‘Jan’ sales range greater than $10,000.

We have entered our ‘lookup_value’ in A19 and placed that cell reference in our ‘MATCH’ formula ‘lookup_value’ (see D19).

Of course, as we would expect our result (C19) is the number ‘3’ since the smallest sales amount greater than $10,000 in our list is $11,000 which occupies the third position in that descending list.

Using inexact match: ‘match_type’ ‘1’

Now that we’ve gotten familiar with both the exact ‘MATCH’ and one of the inexact ‘MATCH’ scenarios, let’s take a look at a scenario in which the inexact ‘MATCH’ using the ‘match_type’ of 1 may be useful.

Consider a case where we would like to set a sales amount cutoff again, but this time we want to find the position in the list that is equal to or the first number below the cutoff.

Let’s use March sales this time and choose our cutoff sales value to be $15,000.

Practically speaking, this will give us the position where all sales numbers below that are definitively less than our cutoff figure.

First things first: We have to sort our March sales column smallest to largest or ascending in order for the ‘MATCH’ function to work.

Then we set or formula to be ‘=MATCH(A23,D9:D12,1)’.

Our result is ‘2’ which means every sales value above and including the second position in the list is less than $15,000.

Reviewing our table closer we find that this amount matches ‘Wayne Mayes’ in the ‘Salesperson’ column.

Inexact match exercise 1

Final thoughts on learning ‘MATCH’

At this point, you may be wondering what the big deal is about finding the relative position of a lookup value in an array really is.

As mentioned at the beginning, the result of a ‘MATCH’ function really comes in handy when used with other functions since it provides that number.

‘MATCH’ simply automates an otherwise mundane process of manually counting.

“The ‘MATCH’ function is most commonly used with the ‘INDEX’ function. If you are not familiar with the ‘INDEX’ function, we have written a tutorial on how to use INDEX together with MATCH right here.”
Kasper Langmann, Co-founder of Spreadsheeto

Knowing what you now know about the output of the ‘MATCH’ function, you could substitute the ‘row_num’ and ‘column_num’ parameters with the result of the ‘MATCH’ function and better automate the ‘INDEX’ function.

So while the usefulness of the ‘MATCH’ function on its own may seem a bit marginal, like many Excel functions, the real power of ‘MATCH’ becomes evident when used within others.