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:

=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:

=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:

=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:

=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:

=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:

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

This works great for lookups in descending sorted data.

Advanced Lookups

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:

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

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

=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:

=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:

=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