Exceljet

Quick, clean, and to the point

This video comes from our online video training for Excel.

How to use the MATCH Function for exact matches

Summary 
In this video, we look at how the MATCH function can find the position of an item in a list. Once you have a position, you can use it to get related values with INDEX.
Video Transcript 

The MATCH function finds the relative position of an item in a list. Match can find exact matches or approximate matches. In this video, we'll look at how to use Match to find an exact match.

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 zero for match type.

In column E, I have list of numbers from one 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 zero, 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 an #N/A error.
 
Now let's look at using MATCH 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 zero 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 zero. 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 placeholder for any one character. So, four question marks give us the position of "kiwi", the only fruit with 4 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.

Author 
Dave Bruns

Related shortcuts

CtrlEnter
Return