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.
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.
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 lookupvalue. 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.
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.
It has been a while since I originally posted my Fuzzy matching UDF’s on the board, and several variants have appeared subsequently. I thought it time to ‘put the record straight’ & post a definitive version which contains slightly more efficient code, and better matching algorithms, so here it is. Firstly, I must state that the Fuzzy matching algorithms are very CPU hungry, and should be used sparingly. If for instance you require to lookup a match for a string which starts with, contains or ends with a specified value, this can be performed far more efficiently using the MATCH function:. Microsoft Excel - Fuzzy Examples.xls Running: 11.0: OS = Windows XP ( F)ile ( E)dit ( V)iew ( I)nsert ( O)ptions ( T)ools ( D)ata ( W)indow ( H)elp.
Comments are closed.
|
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |