Microsoft Excel logo on a green background

When you need to find a value’s exact position in your spreadsheet, you can use the MATCH function in Excel. This saves you from manually searching for the location that you may need for reference or another formula.

The MATCH function is often used with the INDEX function as an advanced lookup. But here, we’ll walk through how to use MATCH on its own to find a value’s spot.

What Is the MATCH Function in Excel?

The MATCH function in Excel searches for a value in the array, or range of cells, that you specify.

RELATED: How to Find the Function You Need in Microsoft Excel

For instance, you might look up the value 10 in the cell range B2 through B5. Using MATCH in a formula, the result would be 3 because the value 10 is in the third position of that array.

Position of a value in a cell range

The syntax for the function is MATCH(value, array, match_type) where the first two arguments are required and match_type is optional.

Advertisement

The match type can be one of the following three options. If the argument is omitted from the formula, 1 is used by default.

  • 1: Finds the largest value less than or equal to the searched value. The range must be in ascending order.
  • 0: Finds the value exactly equal to the searched value and the range can be in any order.
  • -1: Finds the smallest value greater than or equal to the searched value. The range must be in descending order.

You may also see these match types as a tooltip when you build your formula in Excel.

Match type tool tip in Excel

The MATCH function is not case sensitive, allows the wildcards asterisk (*) and question mark (?), and returns #N/A as the error if no match is found.

Use MATCH in Excel

When you’re ready to put the MATCH function to work, take a look at these examples to help you walk through building the formula.

RELATED: Defining and Creating a Formula

Using our example above, you would use this formula to find the value 10 in the range B2 through B5. Again, our result is 3 representing the third position in the cell range.

=MATCH(10,B2:B5)

MATCH used to look up a value's position

For another example, we’ll include the match type 1 at the end of our formula. Remember, match type 1 requires the array be in ascending order.

=MATCH(10,B2:B5,1)
Advertisement

The result is 4 which is the position of the number 9 in our range. That’s the highest value less than or equal to 10.

MATCH with match type 1

Here’s an example using match type 0 for an exact match. As you can see, we receive the #N/A error because there is no exact match to our value.

=MATCH(10,B2:B5,0)

MATCH with match type 0

Let’s use the final match type -1 in this formula.

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

The result is 2 which is the position of the number 11 in our range. That’s the lowest value greater than or equal to 10. Again, match type -1 requires the array be in descending order.

MATCH with match type -1

For an example using text, here we can find Caps in the cell range A2 through A5. The result is 1 which is the first position in our array.

=MATCH("Caps",A2:A5)

MATCH for finding text in Excel

Now that you know the basics of using the MATCH function in Excel, you might also be interested in learning about using XLOOKUP in Excel or using VLOOKUP for a range of values.

Profile Photo for Sandy Writtenhouse Sandy Writtenhouse
With her B.S. in Information Technology, Sandy worked for many years in the IT industry as a Project Manager, Department Manager, and PMO Lead. She learned how technology can enrich both professional and personal lives by using the right tools. And, she has shared those suggestions and how-tos on many websites over time. With thousands of articles under her belt, Sandy strives to help others use technology to their advantage.
Read Full Bio »