How to Use the MATCH Function in Excel (Formula) (2024)

The MATCH function belongs to the list of Excel’s reference/lookup functions. It looks for a value in a lookup array like all the lookup functions do 👀

However, once found, it doesn’t return the corresponding value. But the relative position of the lookup value in the lookup array.

And that’s not it – the MATCH function can do just so much in Excel. So let’s jump into the guide below to learn it all.

Here’s our free sample workbook for this guide for you to download and tag along with the guide ⛵

Table of Contents

  • How to use the MATCH function

  • Approximate match types

  • Other MATCH formula examples

  • Frequently asked questions

How to use the MATCH function

The MATCH function of Excel looks for a given value in an array and returns its relative position from that array 🏆

The function is a really simple one, and you’ll enjoy it as we start exploring it. So let’s dive straight into an example.

Here we have a list of students with their scores in English.

How to Use the MATCH Function in Excel (Formula) (1)

It’s hard to find a student from this list readily. And the larger the list grows, the harder it gets 🥴

Do we have a function that can help us find the position of any student from this list readily Let’s try the MATCH function here to find the position of Addams.

  1. Begin writing the MATCH function as follows.

= MATCH (

How to Use the MATCH Function in Excel (Formula) (2)

  1. Write in the lookup_value as the first argument of the MATCH function.

= MATCH (B7

How to Use the MATCH Function in Excel (Formula) (3)

We are looking for the position of Addams – so that makes our lookup value. In this case, our lookup value rests in Cell B7, so we are creating a reference to it.

  1. Refer to the lookup_range as the second argument.

= MATCH (B7, A2:A5

How to Use the MATCH Function in Excel (Formula) (4)

Where should the MATCH function look for the value “Addams”?

This is the table that contains the student names (Cell A2 to Cell A5).

Do not include the headers in this range. If the headers are included in the lookup range, the relative position of the lookup value would be pushed one position down the list.

How to Use the MATCH Function in Excel (Formula) (5)Kasper Langmann, Microsoft Office Specialist

  1. Define the match_type as 0.

= MATCH (B7, A2:A5, 0)

How to Use the MATCH Function in Excel (Formula) (6)

Pro Tip!

The MATCH function has three different match types 3️⃣

0 – Search for the exact match of the lookup_value.

1 (or omitted) – Search for the largest value less than or equal to the lookup value. The lookup array must be arranged in ascending order for this to work.

-1 – Search for the smallest value greater than or equal to the lookup value. The lookup array must be arranged in descending order for this to work.

The match_type is an optional argument. If omitted, Excel sets it to 1 by default.

For now, we are setting the match_type to 0 as we want Excel to lookup for an exact match. The name Addams is present in the list with the same spelling so the MATCH function can perform an exact match.

  1. Press Enter as we’re done writing the function now 👍

How to Use the MATCH Function in Excel (Formula) (7)

And there you go! Excel finds the relative position of Addams from the list of Students.

Addams is in the second position on the list. As the scores next to Addams are arranged in ascending order, this also tells that Addams scored the second least marks among all 🥈

The MATCH function is not a case-sensitive function. It doesn’t differentiate between uppercase and lowercase letters.

How to Use the MATCH Function in Excel (Formula) (8)Kasper Langmann, Microsoft Office Specialist

Approximate match types

There are three different match types to the MATCH function, and we have only seen one of them yet (the exact match_type).

It’s time that we now look into how the MATCH function works under the other two match types. Let’s take the same example as above – but this time, a little twisted.

For a quick revision, here is the scorecard of the students 📝

How to Use the MATCH Function in Excel (Formula) (9)

Match type (1)

Let’s find which student scored 90 or the next highest mark less than 90.

Must note that for the MATCH function to work with match type 1, the lookup array must be sorted in ascending order.

And take a quick look at our lookup array – it starts from 65 and goes up to 89. Hence, it is already arranged in ascending order.

How to Use the MATCH Function in Excel (Formula) (10)Kasper Langmann, Microsoft Office Specialist

  1. Write the lookup value of the MATCH function as follows:

= MATCH (B7

How to Use the MATCH Function in Excel (Formula) (11)

We want to find students who scored 90 (or nearest to 90 marks). So that makes up our lookup value.

  1. Write in the lookup array as the next argument.

= MATCH (B7, B2:B5

How to Use the MATCH Function in Excel (Formula) (12)

The score is to be looked up from the column of scores. And so this time our lookup array is B2 to B5 🚀

  1. Set the match type to 1.

= MATCH (B7, B2:B5, 1)

How to Use the MATCH Function in Excel (Formula) (13)

Pro Tip!

Why have we set the match type to 1? That’s because we want to find the student who scored 90 marks. Or if there’s no such student, then we want to find the one who scored the highest marks less than 90.

Under match type 1, the MATCH function search for the largest value less than or equal to the lookup value i.e. 90 🔍

  1. And hit “Enter”.

How to Use the MATCH Function in Excel (Formula) (14)

The MATCH function returns 4. Why is that?

Because we have Cheryl with 89 marks at position 4. None of the students scored 90 marks. And the second highest after 90 is 89 marks 🤩

Match type (-1)

To test match type (-1), let’s find which student scored 80 or the least marks greater than 80.

Must note that for the MATCH function to work with match type -1, the lookup array must be sorted in descending order.

How to Use the MATCH Function in Excel (Formula) (15)Kasper Langmann, Microsoft Office Specialist

  1. Sort your lookup array in descending order by clicking on the header (Scores here).
  2. Go to Home Tab > Sort and Filter.

How to Use the MATCH Function in Excel (Formula) (16)

  1. Choose Sort Z to A “Highest to Lowest”

How to Use the MATCH Function in Excel (Formula) (17)

  1. And you have your list sorted in descending order.

How to Use the MATCH Function in Excel (Formula) (18)

Now, to find the student who scored 80 Marks or the next highest marks:

  1. Write the MATCH function with the following changes from above.

= MATCH (B7, B2:B5, -1)

How to Use the MATCH Function in Excel (Formula) (19)

Our lookup value is now 80. And we have set the match type to -1.

  1. Hit Enter and there comes the results.

How to Use the MATCH Function in Excel (Formula) (20)

The MATCH function returns 2. Why is that?

Because at position 2, we have Ana with 82 marks. After 80, we have 82 on the list (the next highest to our lookup value of 80). That’s match type -1 returns 🎯

Other MATCH formula examples

We yet have more Excel MATCH function examples. Let’s look into them here.

In the image below, we have a list of items with different codes to them 📍

How to Use the MATCH Function in Excel (Formula) (21)

From this list, we want to find the position of the Item “CAR”. But we don’t exactly know the code that suffixes it. How can then we find it?

Under the match type (0), the MATCH function can be used with wildcard characters.

How to Use the MATCH Function in Excel (Formula) (22)Kasper Langmann, Microsoft Office Specialist

So even if we do not know the exact code after the item name “cars”, we can use a wildcard character. Let’s do it here then:

  1. Write the MATCH function as follows:

= MATCH (

How to Use the MATCH Function in Excel (Formula) (23)

  1. Write the lookup value (Car) as the first argument of the MATCH function.

= MATCH (“Car*”,

How to Use the MATCH Function in Excel (Formula) (24)

As we don’t know the exact code that comes at the end of the item name, we have used an asterisk at the end 😎

An asterisk represents any number of characters at the end of the item name.

  1. Specify the lookup array as the next argument.

= MATCH (“Car*”, A2:A7,

How to Use the MATCH Function in Excel (Formula) (25)

  1. Set the match type to 0.

= MATCH (“Car*”, A2:A7, 0)

How to Use the MATCH Function in Excel (Formula) (26)

  1. And hit “Enter” to get going.

How to Use the MATCH Function in Excel (Formula) (27)

See that? The MATCH function has found the position of Item CAR-34 from the list. Although we never specified the complete name of the item 💪

Pro Tip!

Must note that there were two items by the name CAR in the list. However, the MATCH function returned to position 2 only 🤔

This is because if there are multiple instances of the lookup value in the lookup array, the MATCH function returns the position of the first instance only.

That’s it – Now what

The guide above teaches us the ins and outs of the MATCH function of Excel. We began learning from a simple example of the MATCH function.

And until now we have seen multiple examples of how to use the MATCH function with different match modes.

The MATCH function is a very commonly used function of Excel. It is one of the easier yet very useful functions of Excel.

And that’s not it. There are many more similar useful functions of Excel that you must know about (even if you’re a beginner) 👦

Like the VLOOKUP, SUMIF, and IF functions of Excel. To learn them, enroll in my 30-minute free email course that will teach you these (and many more) functions of Excel.

Other resources

You’d often see the MATCH function is used together with the INDEX function. Both of these functions together to work like an advanced lookup function.

In addition to these, other lookup functions of Excel include the HLOOKUP, VLOOKUP, and XLOOKUP functions of Excel.

Frequently asked question

Is MATCH better than VLOOKUP?

No. The MATCH function only returns the relative position of a lookup value in a lookup array and not the value itself.

To get the corresponding value for a look-up value, the MATCH function must be used together with the INDEX function.

However, the VLOOKUP function can do all of this alone.

What is the MATCH function in Excel?

The MATCH function is a lookup/reference function of Excel.

It looks up for a given value in a look-up array. And if found, it returns the relative position of the lookup value in the lookup array.

Kasper Langmann2024-02-29T10:10:09+00:00
How to Use the MATCH Function in Excel (Formula) (2024)
Top Articles
Latest Posts
Article information

Author: Aron Pacocha

Last Updated:

Views: 6572

Rating: 4.8 / 5 (48 voted)

Reviews: 87% of readers found this page helpful

Author information

Name: Aron Pacocha

Birthday: 1999-08-12

Address: 3808 Moen Corner, Gorczanyport, FL 67364-2074

Phone: +393457723392

Job: Retail Consultant

Hobby: Jewelry making, Cooking, Gaming, Reading, Juggling, Cabaret, Origami

Introduction: My name is Aron Pacocha, I am a happy, tasty, innocent, proud, talented, courageous, magnificent person who loves writing and wants to share my knowledge and understanding with you.