October 20, 2021

MATCH function in Excel

In this lesson we will learn the use of MATCH function in Excel. The MATCH function is used to get the position of a specific value provided by the user.

=MATCH(value, list_range, match_type)
  • value (required argument) – This is the value we want to look up.
  • list_range (required argument) – The data range that is to be searched.
  • match_type (optional argument) – It can be set to 1, 0, or -1 to return results as given below:
    • Here 0 denotes exact match. This is mostly used
    • Just for knowledge, other than 0, it can be 1 : To find the largest match less than or equal to the value provided. List must be sorted in ascending order
    • or can be -1 : To find the smallest match greater than or equal to the value provided. List must be sorted in descending order

Let’s understand with a simple example. In the below table we provided the value “white” in the the following MATCH function in cell C2.


The position of the value “white” in the list A2:A5 has been returned as 4.

In the MATCH function, the list_range can be horizontal or vertical. Also the value can be referred from any other cell. Below is the example of horizontal list(B1:E1) where D4 is the cell reference for the value “red”. Below is the MATCH function added in cell B4 which returns the position as 1.


Note :
  • The MATCH function is Case Insensitive, means irrespective of the case of the text it will match the value in the list and returns the position if found.

You can check here to understand how the MATCH function works in Google Sheet.

Leave a Reply

Your email address will not be published. Required fields are marked *