MATCH function

Excel MATCH Function is a powerful tool that helps you find the relative position of a specified value within a range of data. It’s like a treasure map for your spreadsheet, guiding you to the exact location of the information you’re looking for.


=MATCH(lookup_value, lookup_array, [match_type])


Let’s break down the function’s arguments:

lookup_valueThe value you want to find in the lookup_array.
lookup_arrayThe range of cells where you want to search for the lookup_value.
[match_type](Optional) An optional argument that specifies the type of match to perform. It can have three values:
  • 1 or omitted: Finds the largest value less than or equal to the lookup_value (default). 0: Finds an exact match. -1: Finds the smallest value greater than or equal to the lookup_value.

How to use

Now, let’s see how you can use the MATCH function with some examples:

Example 1: Finding an exact match

This formula searches for the exact match of “Apple” in cells A1 through A5. If “Apple” is in A3, it will return 3.

Example 2: Finding the nearest smaller value

Here, the function looks for the largest value less than or equal to 25 in cells B1 through B5. If it finds 24 in B3, it returns 3.

Example 3: Finding the nearest greater or equal value

This time, the function searches for the smallest value greater than or equal to 35 in cells C1 through C5. If it finds 38 in C4, it returns 4.

Remember, the MATCH function is incredibly handy when you need to locate data in your Excel spreadsheet. Whether you’re looking for an exact match or trying to find the nearest value, it can save you time and effort by pinpointing the information you need.

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.