# How to Use the Excel MATCH Function Like a Pro

The MATCH function is one of Excel’s lookup functions that allows you to find the position of a lookup value within a range of cells This handy little function has become an indispensable tool for me and many other Excel power users

In this comprehensive guide, I’ll walk you through everything you need to know to master the MATCH function. By the end, you’ll be using MATCH like a pro to look up and retrieve data in Excel like a boss!

## What is the MATCH Function?

The MATCH function searches a range of cells to find the position of a lookup value. It returns the relative position of the matched cell, not the value itself.

For example, if you have the numbers 5, 25, and 38 in cells A1 A3, the formula =MATCH(25,A1 A3,0) would return 2, because 25 is the 2nd item in the range

The basic syntax of the MATCH function is:

excel

``=MATCH(lookup_value, lookup_array, [match_type])``

The arguments are:

• lookup_value – The value you want to match in the lookup array. This can be a number text logical value, or a cell reference.

• lookup_array – The range of cells being searched.

• match_type (optional) – Specifies how the match is performed. 0 for exact match, 1 for approximate match (default), and -1 for approximate match in reverse order.

The MATCH function is extremely useful for finding the position of items within a range or table. This position can then be fed into other functions like INDEX to retrieve data from a specific location.

## When to Use the MATCH Function

Here are some common situations where the MATCH function comes in handy:

• Finding the position of a value within a single column or row range.

• Getting the column number or row number to use in another function like INDEX.

• Looking up a value when the lookup array is out of order.

• Finding the position of the largest or smallest value near a lookup value.

• Locating duplicate values and their positions within a range.

• Performing advanced lookups with multiple criteria.

• Doing a reverse lookup by finding the position of the closest value greater than or equal to a lookup value.

As you can see, MATCH is useful for both basic and more complex lookup scenarios in Excel.

## How the MATCH Function Works

The key to understanding MATCH is that it searches an array and returns the relative position number of a found cell, not the cell value itself.

The match_type argument determines how MATCH performs comparisons between values:

• 0 finds an exact match
• 1 finds the next smallest value (default behavior)
• -1 finds the next largest value

If an exact match is found, MATCH will return its position regardless of the match_type setting.

By default, MATCH performs an approximate match, returning the position of the largest value less than or equal to the lookup value.

When match_type is 1 or -1, the lookup array must be sorted in ascending or descending order respectively.

If no match is found, MATCH returns the #N/A error value.

## Simple MATCH Examples

Let’s start with a few straightforward examples of using the MATCH function.

Given this dataset:

| Number |
|-|-
| 5 |
| 25 |
| 38 |

The formula `=MATCH(25,A2:A4,0)` returns 2, the position of 25.

`=MATCH(39,A2:A4,1)` returns 2, the position of 38 which is the next smallest.

And `=MATCH(5,A2:A4,-1)` results in #N/A because the data is not sorted in descending order.

## Uses for MATCH

Now that you understand the basics of how the MATCH function works, let’s go over some of the most common and useful applications of MATCH in Excel.

### Lookup Value Position in Row/Column

One of the simplest and most common uses of MATCH is to find the position of a lookup value in a single row or column range.

For example, given this data set of numbers in A1:A10, you can use MATCH to find the position of 25 like this:

excel

``=MATCH(25,A1:A10,0)``

This returns 6, the position of 25 within the lookup array.

The same approach works for looking up text values in a single column too.

### Get Row/Column Number for INDEX

MATCH is frequently combined with INDEX to perform lookups. It supplies the row or column number for the INDEX function to return the value at that intersection.

Given this simple data table:

| | A | B |
|-|-|-|-
| 1 | Bananas | 25 |
| 2 | Oranges | 38 |

The formula `=INDEX(B1:B2,MATCH(38,A1:A2,0))` returns 38.

MATCH provides the position 2 for INDEX to retrieve the corresponding value in column B.

### Lookup in Unsorted Data

One great thing about MATCH is it can find the position of lookup values even when the lookup array is out of order.

Just set match_type to 0 for an exact match:

excel

``=MATCH(25,A2:A5,0)``

As long as 25 appears somewhere in the range, MATCH will find and return its position.

### Approximate Match

When you need to find the closest value less than or greater than a lookup value, use match_type 1 or -1 respectively.

For example, let’s say you need the position of the next smallest number after 43 in a sorted range. Use MATCH like this:

excel

``=MATCH(43,A1:A10,1) ``

MATCH will return the position of the closest smaller number.

For descending sorted data, use -1 instead to find the next largest number.

### Lookup Duplicates

An exact MATCH can also help you find all duplicates and their positions within a range.

For example, to return all occurrences of the number 25:

excel

``=MATCH(25,A:A,0)``

If there are multiple 25s, MATCH will return the position of the first one. To find second and third instances, add additional MATCH calls using the range offset from previous matches.

### Reverse Lookup

To perform a reverse lookup by finding the closest greater than or equal to a value, use match_type -1.

For example, to find the position of the next largest number after 43:

excel

``=MATCH(43,A1:A50,-1)``

This works great for lookups in descending sorted data.

With some additional logic and functions, MATCH can handle more complex lookups like:

• Multiple criteria
• Partial matches
• Case-sensitive text
• External references
• And more!

## Lookup Left, Right, Above, Below

MATCH only finds values within the one-dimensional lookup array you provide. But you can easily use MATCH to find positions to the left, right, above or below a cell.

For example, this lookup finds the position of the cell just above:

excel

``=MATCH(A2,OFFSET(A2,-1,0),0)``

And to get the position of the next cell to the right:

excel

``=MATCH(A2,OFFSET(A2,0,1),0) ``

Use minus signs to look left/up and positive signs to look right/down.

## Match Multiple Criteria

While MATCH can only look in one array at a time, you can use additional functions to add more criteria.

For example, to find the position of “Bananas” only when the count is also 25:

excel

``=MATCH(1,(B2:B5="Bananas")*(C2:C5=25),0)``

The multiplication acts like an AND operator, requiring both criteria.

## Lookup Based on Cell Value

To make your lookup dynamic based on another cell’s value, just reference that cell instead of a hardcoded value.

For example, to lookup the position of the value in A1:

excel

``=MATCH(A1,B1:B10,0)``

Now the lookup will change based on what’s entered into cell A1.

## Limitations of MATCH

While super useful, the MATCH function does have a few limitations to be aware of:

• Only searches 1D ranges (single rows or columns)
• Always returns first match if multiple found
• Limited to 255 characters for text lookups
• Not case-sensitive for text

In many cases, you can work around these limitations with some creative additional formulas.

## MATCH vs Index/Match vs VLOOKUP

MATCH is often combined with

## MS Excel – Match Function

What is a match function in Excel?

The MATCH function searches for a specified item in a range of cells, and then returns the relative position of that item in the range. For example, if the range A1:A3 contains the values 5, 25, and 38, then the formula =MATCH (25,A1:A3,0) returns the number 2, because 25 is the second item in the range.

Can the match function be combined with other functions in Excel?

Yes, the MATCH function can be combined with other functions in Excel, such as the INDEX function, to return values based on a specific position. For example, you can use the MATCH function to find the position of a person’s name in a list, and the INDEX function to return their corresponding phone number.

When should I use a match function?

Tip: Use MATCH instead of one of the LOOKUP functions when you need the position of an item in a range instead of the item itself. For example, you might use the MATCH function to provide a value for the row_num argument of the INDEX function. MATCH (lookup_value, lookup_array, [match_type]) The MATCH function syntax has the following arguments:

What is xmatch function?

The MATCH function can perform exact and approximate matches and supports wildcards (* ?) for partial matches. There are 3 separate match modes (set by the match_type argument), as described below. Note: the MATCH function will always return the first match. If you need to return the last match (reverse search) see the XMATCH function.