Excel MATCH Function

HomeExcel Functions List (Top 100) Examples + Sample FileExcel MATCH Function (Example + Sample File)

What is EXCEL MATCH FUNCTION

The Excel MATCH Function is listed under Microsoft Excel's Lookup Functions category. It returns the index number of the value from an array. In simple words, the MATCH function looks up for a value in the list and returns the position number of that value in the list.

How to use it

To learn how to use the MATCH function in Excel, you need to understand its syntax and arguments:

Syntax

MATCH (lookup_value, lookup_array, [match_type])

Arguments

  • lookup_value: The value whose position you want to get from a list of values.
  • lookup_array: The range of cell or an array contains values.
  • [match_type]: The number (-1, 0 & 1) to specify how excel look for the value from the list of values.
    1. If you use 1, it will return the largest value which is equal or less than the lookup value. The values in the list must be sorted in ascending order.
    2. If you use -1, it will return the smallest value which is equal or greater than the lookup value. The values in the list must be sorted in ascending order.
    3. If you use 0, it will return the exact match from the list.

Notes

Example

To master the MATCH function we need to try it out in an example, so make sure to check out the below one:

In the below example, we have used 1 as match type and we are looking for value 5.

As I have already mentioned if you use 1 in match type it returns the largest value which is equal or smaller than the lookup value. In the entire list, there are 3 values that are smaller than 5 and 4 is the highest in them.

That's why in the result it has returned 3 which is the position of value 4.

Related functions

This tutorial is the part of our Excel Functions with Examples (Function Guide) and below are some of the related functions:

About the Author

Puneet Gogia

Puneet is using Excel since his college days. He helped thousands of people to understand the power of the spreadsheets and learn Microsoft Excel. You can find him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.