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 the lookup_array is “sales”. Now we need to specify the match_type. Because it’s unlikely that the lookup_value will be in the commission table, we need to use an approximate match. 0 is only for exact matches, so we have two options: 1 and -1. Follow this simple rule to choose the right approximate match_type: If the values in the lookup list are in ascending order, use 1. If the values appear in descending order, use -1. In this case, the values are in ascending order, so we want a match_type of 1. Technically, the 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 function 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 the lookup_value. For example, for $74,000, MATCH moves through the list until it finds a value that’s larger than $74,000, 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 the 3rd item 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 the match even though the match_type is approximate. Finally, when looking up values greater than the last value in the lookup_array, MATCH will return the position of the last value. 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
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
