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