Skip to content

How to Use the MATCH Function

In this article, we will explain how the MATCH function works, how to use it, and give you two practical examples.

How the MATCH Function Works

The MATCH function searches for a specific item in a list of values. Once the MATCH function finds that value, it returns that item’s relative position within the searched set of values.

For instance, let’s say you have the following list of colors.

  • Blue
  • Red
  • White
  • Green
  • Yellow
  • Purple

If you utilized the MATCH function to look for the value of “Green,” the function would return the number 4 because “Green” is the fourth color in the list.

How to Use the MATCH Function

The MATCH function accepts up to three arguments.

=MATCH(lookup_value, lookup_array, match_type)

The first argument is the lookup value, and this is the value that you want the MATCH function to find. This argument is required.

The second argument is the lookup array or the list of values you want the MATCH function to look through. This argument is required.

The third and final argument is the match type. The value for this argument can be 1, 0, or -1. Each of these arguments will have the MATCH function search for the lookup value differently.

If the Third Argument is 0

If the third argument is 0, the MATCH function will look for the exact value it is searching for. In most cases, this is what you want.

If the Third Argument is 1

If the third argument is 1, the MATCH function will return the largest value within the list that is less than or equal to the lookup value. For this type of match to succeed, the list of values must be in ascending order (1, 2, 3, 4, etc.).

Here is how it would work. Let’s say you have the following list of numbers.

  • 10
  • 20
  • 30
  • 40

Now let’s say that you want the MATCH function to search for the value 25, and you make the third argument a 1. The MATCH function will look through the list and return a value of 2.

Among the values in the list above, the value of 20 is the largest value that is also less than or equal to the search value of 25. The number 20 is in position 2, so the MATCH function returns the value of 2.

If the Third Argument is -1

If the third argument is -1, the MATCH function will return the smallest value within the list that is greater than or equal to the lookup value. For this type of match to succeed, the list of values must be in descending order (4, 3, 2, 1, etc.).

Here is how it would work. Let’s say you have the following list of numbers.

  • 40
  • 30
  • 20
  • 10

Now let’s say that you want the MATCH function to search for the value 15, and you make the third argument a -1. The MATCH function will look through the list and return a value of 3.

Among the values in the list above, 20 is the smallest value that is also greater than or equal to the search value of 15. The number 20 is in position 3, so the MATCH function returns the value of 3.

The Default Third Argument

It is important to note that the value of 1 is used for the third argument by default if left blank. If you are looking for an exact match, be sure to type in the value of 0 for the third argument.

Two Practical Examples

Here of two practical examples of the MATCH function being used.

Example 1 – Return the Month Number

Let’s say you want to look through a list of months and return that month’s number. All you need to do is use the MATCH function.

With the months listed in column A, you place the MATCH function in cell C3 and have it search for “June.” Notice that the third argument is set to 0 because you want an exact match.

The MATCH function locates June in the sixth position and returns a value of 6.

Example 2 – Return the Nearest Number

Let’s say you want to look through a list of numbers in descending order and return the position of the smallest number greater than or equal to 65. All you need to do is use the MATCH function.

With the numbers listed in column A, you place the MATCH function in cell C3 and have it search for the value 65. Notice that the third argument is set to -1 because you want the position of the smallest number greater than or equal to the lookup value.

The MATCH function sees that 70 is the smallest number greater than or equal to 65 and returns a final value of 4.

This Post Has 0 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back To Top