Hi everyone, Today we are going to discuss MATCH Function of Excel Sheet.In this article, We would learn how to use Match Function and how it works. Match Function searches the entire range of cells for a particular item and returns its position.It supports approximate and exact match. Let us discuss how can we use it.
What does MATCH Function do?
Match Function is yet another way to look up into rows and columns and get the desired data from the range of the array, but there is a bit difference, unlike V lookup function Match Function returns the relative position of the desired data.It searches the array of cells and returns the respective position of the data.
How to use MATCH Function in Excel?
A syntax for Match Function:
=MATCH(lookup_value, lookup_array, [match_type])
- lookup_value: It is an essential field.Lookup_value is that value for which we are looking.It can be anything a number, text, cell reference or any logical value.
- lookup_array: Lookup_array is the range of cells, rows or columns through which we need to search for the lookup_value.It is also a mandatory entity.
- [match_type]: It is an optional argument.It can have values -1,0 or 1. It defines the criteria for matching lookup_value with values in the lookup_up array.By default, it is 1.
MATCH TYPE CRITERIA:
- Match_type 0: In this, Match Function tries to find out the exact match for the lookup_value.Here, Match Function finds the value that is exactly equal to lookup_value.
- Match_type 1: Here, Match Function finds the largest value that is less than or equal to the lookup_value.It comes under approximate match.The values in the lookup_array here must be arranged in ascending order.
- Match_type -1: Match Function finds the smallest value that is greater than or equal to the lookup_value.The values here must be arranged in descending order that is TRUE, FALSE, Z-A,…2,1,0,-1,-1….and so on.
NOTE: Match Function returns the position of the lookup_value from the lookup_array, not the value itself.
Let us understand Match Function through an example, Consider this Excel Sheet, Wherein Scores of students are arranged in Descending order,
Let’s write Match Function for Laura to see where she stands in the class,
- lookup_value: We would first define our lookup_value that is “Laura” in double quotes as it is a text string,
2. lookup_array: As stated above,lookup_array would define the range of cells to look up in for the lookup_value,
3.[match-type]: Now, as we want to find out the rank of Laura in class, we would need exact matching, that is why we would define 0 as the match-type, also we would not require any sorting for 0 match type,
As you can see below, applying MATCH Function would return the position of the “Laura” in the class, you would observe that our lookup_value was “Laura” and Match Function returns its relative position to us.
That is How you can use MATCH Function in Excel to find the position of any value in a specific range of the array.We hope this article has helped you in understanding the concept of Match Function.Share it if you find it helpful and help your friends and colleagues also who are new to Excel, Practice more to learn things in a better way.Write your queries to us anytime, we are here to help you.