Excel Function: MATCH() Explained

Excel MATCH function examples allow us to see how the MATCH function can be used, and understand what scenarios it could help us.

Basically, the MATCH function was designed to return a relative row reference (relative to the array in which data is searched for) for data that you specify. If searching for a number, you have the option of finding numbers that are less than or equal to, greater than or equal to, and exactly the value you specify.

First, let’s look at the syntax.

=MATCH(value, array, match_type)

Value is simply what you’re looking for. It can be a number, or a string of text. If you’re searching for a number (and yes you can use cell references e.g. A4) you just put it there like this =MATCH(24, array, match_type) or =MATCH(A4, array, match_type).

How to Use INDEX MATCH

Array is the range reference of cells that you’d like to conduct your search in. In other words, where do you want MATCH to look for the value you specified. It would be something like A2:A100.

Match_type is one of three options, and is optional. If you omit the value, the default selection will be used.

  • 1 – MATCH will find the largest value that is less than or equal to value. So if the value you’re searching for does not exist, you’ll get the nearest value that is less than the value you specified. For this to work best, you should sort your data in ascending order first. This is also the default option, so if you omit a match_type this is the behavior you’ll get.
  • 0 – This option will insure that MATCH only returns a relative row when an exact match is found. Your array can be sorted in any way. If no match is found, you’ll get N/A.
  • -1 – The last option gives you the first value greater than or equal to the searched for value. You should sort your array in descending order. If an exact match isn’t found, you get the next largest number.

Wild Cards

Excel MATCH function examples often include the use of wild cards. These are place holders that allow data to be matched with a broader criteria. Consider the following list:

Learn to Use INDEX MATCH MATCH

A

1 Ken Walker

2 Luke Skywalker

3 Karl Stalker

Let’s see how the MATCH function wild cards react to this data.

=MATCH(“K*alker”, A1:A3, 0) – this would theoretically match “Ken Walker” and “Karl Stalker” because they both start with “K” and end with “alker”. The * matches any number of characters.

On the other hand, the? matches any one character. So…

A

1 A3440

2 C1036

3 B2140

4 B2236

=MATCH(“???40”, A1:A4, 0) – this would actually match “A3440” first and return 1 since A3440 is in the first row of our array (it also happens to be in the cell A1).

On the other hand, =MATCH(“B??36”, A1:A4, 0) would return 4.


Also See:

Show Comments

No Responses Yet

Leave a Reply