How to use the MATCH Function for exact matches

Abstract

TheMATCH functionfinds 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.

In column E, I have a list of numbers from 1 to 9.

I’ve already created anamed rangecalled “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.

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 theMATCH functionwith 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 theMATCH functionand point to the lookup values.

For all formulas, I’m using 0 for match_type for exact matching.

Now it’s possible for you to 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.

Finally, note that MATCH supportswildcardswhen the match throw in 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.

And two question marks + “mes” gives us the position of “limes”.

As we saw earlier, MATCH returns #N/A if no match is found.