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

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
abbreviate names1

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 ...

Convert State Names To Abbreviations
abbr state names1

Assume that you got a task to convert the full state's name into the abbreviations in MS Excel, and for doing this task, you might choose to do it manually, which is an acceptable way only if you don't have ...

Filter or Remove Columns
filter remove columns1

Assume that in MS Excel, you have a table consisting of a few columns consisting of few values, and you want to filter to remove the specified columns from the table. You might take it easy and would prefer to ...

Filter or Extract for Multiple OR Criteria
filter with multiple criteria1

If you are an valid MS Excel user, you have probably come across a situation where you wanted to filter the data in a separate table with specific criteria. You could do this task manually, which is also acceptable when ...

Filter Multiple Values
filter multiple value1

In Excel, you can easily filter a table to display only the rows that meet your criteria. This is a quick way to find the information you need without scrolling through all the data. In this post, we'll show you ...

Excel XLOOKUP Function

Excel XLOOKUP Function was added into Excel as a beta feature in August 2019 and is now accessible exclusively in Microsoft 365. (as of July 2021). However, if you fall into this category and often deal with big sets of ...

Extract or Filter Top n values
filter on top n values

You might have been through this kind of situation where you need to filter out the top n values from the list having few values, and I am also pretty sure about it that you might have chosen to do ...

Extract Unique Items From A List
Extract Unique Items From A List In Excel1

This post will guide you how to extract unique itmes from a given list in Microsoft Excel. How to create a newly formula to get unique values from a range cells in Excel. The unique list of items is the ...

Sidebar