The MATCH function takes three arguments: the lookup_value, which is the value you’re looking up, the lookup_array, which is the list to look in, and match_type, which specifies exact or approximate matching. In this example, we’ll use MATCH to find exact matches, so we’ll be using 0 for match_type. In column E, I have a list of numbers from 1 to 9. I’ve already created a named range called “numbers” that refers to this list. In cell C7, I’ll enter the MATCH formula. For lookup_value, I’ll use C6, so we can easily change it later. For lookup_array, I’ll use “numbers”, and for match_type, I’ll use 0, to indicate exact match. When I enter the formula, MATCH returns 1. If I enter 3 as the lookup_value, MATCH returns 3. MATCH gives us the relative position of the item in the list. MATCH returns 3 because 3 is the third item in the list. I can demonstrate this by moving 3 to another place in the list. At each new location, MATCH gives us the relative position of 3 in the list. And, if I re-sort the list MATCH again returns 3. What if the list contains duplicates? In that case, MATCH returns the position of the first item only. What if an item doesn’t exist in the list? In that case, MATCH returns the #N/A error. Now let’s look at the MATCH function with text. Here we have a named range called “fruit”. In the table to the left, I have some lookup values already entered. In column C, I’ll enter the MATCH function and point to the lookup values. For all formulas, I’m using 0 for match_type for exact matching. Now you can see a few other features of the MATCH function. First, notice that MATCH is not case-sensitive. MATCH returns 2 for “pears” regardless of the case. Next, like we saw with numbers, when there are duplicates in the list, MATCH will return the position of the first item. Finally, note that MATCH supports wildcards when the match type is 0. The asterisk is a placeholder for one or more characters. So, “ban” plus an asterisk returns 10, the position of bananas. We can get the position of grapes with “*pes”. A question mark is a wildcard for any one character. So, four question marks give us the position of “kiwi”, the only fruit with four characters. And two question marks + “mes” gives us the position of “limes”. As we saw earlier, MATCH returns #N/A if no match is found.
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.
