The MATCH function in Excel is **useful when you need to give the relative position of an item within a range of cells.**

This function is commonly used along with the INDEX function to create a powerful lookup formula.

##### Table of Contents

The rules for using the `MATCH`

function in Excel are as follows:

- The function requires a search key, range, and search type.
- The function then outputs the relative position of an item specified by the given parameters.

Let’s take a look at a quick example where we can use the `MATCH`

function. in Excel

We have a list of values in ascending order, and we have a value to search for in this range. For example, in an array of ages, we want to know whether anyone is aged 30. Afterward, we want to know what position that person is on the list.

Is it possible to get the position of the value that matches what we’re searching for?

With the `MATCH`

function, it becomes quite easy to get the relative position of a matched value. The `MATCH`

function can also be used along with the `INDEX`

function to provide a `VLOOKUP`

functionality that is not restricted to matching with the first column of a table.

Now that we know when to use the `MATCH`

function in Excel, let’s dive into how to use it and work on an actual sample spreadsheet.

**The Anatomy of the MATCH Function**

The syntax of the `MATCH`

function in Excel is as follows:

=MATCH(lookup_value, lookup_array, [match_type])

Let’s dissect this formula and understand what each of these terms means:

**=**the equal sign is how we start any given function in Excel.**MATCH()**is our`MATCH`

function. It returns the relative position of an item in a range.**lookup_value**refers to the value that you want to match in lookup_array. The lookup value can be a value or a cell reference.**lookup_array**refers to the range of cells being searched**match_type**refers to how Excel matches lookup_value with the range of values in lookup_array. By default, this argument is set to 1.- A match_type of 1 indicates that
`MATCH`

will find the largest value that is less than or equal to the lookup_value. - A match_type equal to 0 indicates that
`MATCH`

will find the first value that is exactly equal to the lookup_value. - Having a match_type of -1 indicates that
`MATCH`

will find the smallest value that is greater than or equal to the lookup value.

**A Real Example of Using MATCH Function**

Let’s take a look at a real example of the `MATCH`

function being used in an Excel spreadsheet.

In the example below, we have a table of different raw meats as well as the amount in kilograms we have available. The `MATCH`

formula in row 8 searches for a cell in the range **B2:B5 **that is equal to 11. The formula correctly indicates that the 2nd row in the range equals 11.

The `MATCH`

function in row 9 uses a different method of search. In this formula, we’re looking for the lowest value in the range **B2:B5**, which is greater than or equal to the value of 13. This leaves us with 25, which is the 1st value in the range.

In the example below, we have another list of items. The list is now arranged in ascending order this time. In our first `MATCH`

formula, we tried to find an exact match of 100. Since no matches were found, the formula returned an error. In the second `MATCH`

formula, we searched for the highest value less than or equal to 50. Our formula returns the value of 2, which points to the 2nd value in our range.

You can make your own copy of the spreadsheet above using the link attached below.

If you’re ready to try out the `MATCH`

function in Excel, let’s begin writing it ourselves!

**How to Use MATCH Function in Excel**

This section will guide you through each step needed to start using the `MATCH`

function in Excel. You’ll learn how we can use the `MATCH`

function to get the relative position of an item that matches a provided lookup value.

- First, select the cell where we will place our
`MATCH`

function. In this example, we’ll place the formula in cell**A8**.

- Next, we just simply type the equal sign ‘
**=**‘ to begin the function, followed by the string ‘**MATCH(**‘.

- Fill in the arguments of the
`MATCH`

function. In the example below, we specified a lookup value of 10, a lookup range of**B2:B5**, and a search mode of 0 (which works for descending values in a range).

In the next part of this guide, we’ll briefly explore how you can use the `MATCH`

and `INDEX`

functions together.

- We can use the
`INDEX`

function to return a specific value in the same row as the matched value. In this example, we want to return the corresponding value in column A. We simply have to specify the range**A2:A5**and the result of our`MATCH`

function.

- Hit the
**Enter**key to evaluate the function. We now returned the corresponding type of the amount we matched with.

**Frequently Asked Questions (FAQ)**

**Why does my MATCH formula return an error?**

If the`MATCH`

function fails to find a match in the provided range, the function will return a #N/A error.**Is the MATCH formula case-sensitive?**

The`MATCH`

function does not distinguish between uppercase and lowercase letters. You also have the option to use wildcard characters such as the “?” and “*” symbols. The question mark matches any single character, while an asterisk matches any sequence of characters.

That should be everything you need to remember when working with the `MATCH`

function in Excel. This step-by-step guide shows how simple it is to search and get the relative position of a value in a given range.

The `MATCH`

function is just one example of a useful function in Microsoft Excel. With so many other Excel functions out there, there will be plenty of options for you to create more powerful worksheets.

Are you interested in learning more about what Excel can do? Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.