Excel Match Function

This post will guide you how to use Excel MATCH function with syntax and examples in Microsoft excel.

Description

The Excel MATCH function search a value in an array and returns the position of that item.

The MATCH function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.

The MATCH function is available in Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000, Excel 2011 for Mac.

Syntax

The syntax of the MATCH function is as below:

= MATCH  (lookup_value, lookup_array, [match_type])

Where the MATCH  function arguments are:
Lookup_value -This is a required argument.  The value that you want to search.
Lookup_array – This is a required argument.  The data array that is to be searched.
Match_type – This is an optional argument.  This value can be set as: 1, 0, -1. The default value is 1.

Note:  
1 – the MATCH function will search the largest value that is less than or equal to Lookup_value
0 – The MATCH function will search the first value that is exactly equal to Lookup_value
-1 – The Match function will search the smallest value that is greater than or equal to Lookup_value.

Example

The below examples will show you how to use Excel MATCH  Lookup and Reference Function .

#1 =MATCH(35,A1:A4,1)

This Excel Formula will lookup the value “35” and find the largest value that is less than or equal to “35”, it will return “2”, As the value “34” is the only  largest value that is less than to “35”. So it will return the position of value “34” in range “A1:A4”.

excel match function example1

#2 =MATCH(35,A1:A4,0)

This Formula will return the “#N/A” value, As the Match type is set to “0”, it means that the function will find the first value that is exactly equal to “35”, but it is not able to find the value “35” in “A1:A4” range.

excel match function example2

#3 =MATCH(35,A1:A4,-1)

This Formula will return “2”, if the Match type is set to “-1”, the Match function will find the smallest value that is greater than or equal to “35”in range “A1:A4”, then return its position.

excel match function example3

More Excel MATCH Formula Examples


  • Finding the Max and Min value in an Alphanumeric Data
    if you want to find the Maximal or minimal string value from an alphanumeric data list in the range B1:B5, you can create a formula based on the LOOKUP function and the COUNTIF function.….
  • Convert Weekday Name to Number
    Assuming that you have list of data B1:B7 that contain weekday names, you want to convert all weekday names to numbers.You can use a formula based on the MATCH function to achieve the result
  • Find Missing Numbers in a Sequence in Excel
    You can use an excel array formula based on the SMALL function, the IF function, the ISNA function, the MATCH function, and the ROW function to find missing numbers in a sequence…
  • Find Closest Value or Nearest Value in a Range
    You need to use an excel array formula based on the INDEX function, the MATCH function, the MIN function and the ABS function to find Closest Value or Nearest Value in a Range in Excel…
Related Posts

Match Single Criteria in Excel

No repeat statistic based on a single condition is used wildly in daily work, for example, count type of products, the duplicate types are not encountered. This article describes the way of no repeats statistics for data in a single ...

Excel Array Operation

We have all heard of arrays. Depending on the dimensionality, they are divided into one-dimensional arrays and two-dimensional arrays. Depending on the data type, they can also be divided into numeric arrays, logical arrays, and so on. The concatenation and ...

Break ties with helper COUNTIF and column

Suppose you got a task to adjust the values that contain the ties; what would be your first attempt to break the ties of the given value? If you are wondering about doing this task manually, let me add that ...

Find the Closest Data to the Data Provided in Excel

In our daily work, we may encounter such an issue that to find the closest value to a certain value. In fact, Excel internal functions can help us solve this problem. In today’s article, we will show you how to ...

Abbreviate Names Or Words in Excel

As an MS Excel user, you might have come across a task where you need to abbreviate different names or words, and there are also possibilities that you might have done this task manually by assuming that there isn't any ...

Comments

So empty here ... leave a comment!

Leave a Reply

Sidebar