Quick, clean, and to the point

How to use MATCH to find approximate matches

In this video, we'll look at how to use the MATCH function to find approximate matches. This is useful for things like determining a commission tier based on a sales number, figuring out a tax rate based on income, or calculating postage based on weight.

Let's take a look.

In this example, we have a table of commission rates on the right and a list of sales numbers in a table on the left. I've already created a named range for the Sales numbers in column F.

Let's use MATCH to look up the correct commission tier for each sales number.

I'll start off entering the MATCH formula in C6. The match value comes from column B, and lookup array is "sales".

Now we need to specify match type. We need an approximate match, since it's unlikely that the lookup value will be in the commission table.

Zero is only for exact matches, so we have two options: 1 and -1.

Follow this simple rule to choose the right match type: If the lookup list is sorted ascending order, use 1. If values are in descending order, use -1.

In this case, the values ascending, so we want a match type of 1. Technically, match type is an optional argument, and defaults to 1, but I'm going to add it to the function to keep things clear.

When I copy the formula down, MATCH returns the position of the match found in the Sales list.

Like VLOOKUP, with a match type of 1,  MATCH will find the largest value that is less than or equal to lookup_value. For example, for 74000, match moves through the list until it finds a value larger than 74000, then it moves back to 50,000, which is the first position in the list.

With 123,000, MATCH moves through the values until it hits 125,000, then it drops back to 100,000 which is 3rd in the list. And so on.

You can see why it's important that the list be sorted in ascending order.

There are a few more things to notice here. First, if you happen to have an exact match, MATCH will return the position of that match even though the match type is approximate.

Next, any value lower than the first value in the lookup array will return NA.

Finally, when looking up values greater than the last value in the lookup array, match will return the position of the last value.

Finally, you've probably noticed that we're only looking up the correct position in the table, not the actual commission rate. We'll do that next when we combine the MATCH function with the INDEX function.

Dave Bruns

Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.