Excel Match Function
In this tutorial, we will learn about Match Function in Excel with the example. We will also learn a more about how this function works when we combine with Vlookup and Match.
Basically, Match Function search for a specified item in a range of cells and then returns the relative position of that item in the range.
So let’s start with the following example:-
In this image, we can see that this is a table which contains Column A “Product” and in Column B “Count” and in Column C there is a product name from the product list “Apples”. The use of Match Function is to find the position of “Apples” in the list and in this list, the position of “Apples” is “3”.
Now we will learn by break the argument of Match Function as follows:-
MATCH(lookup_value, lookup_array, [match_type])
The MATCH function syntax has the following arguments:
lookup_value – The value to match in lookup_array.
lookup_array – The range of cells being searched.
match_type – Optional. The number -1, 0 or 1. The match_type argument specifies how Excel matches lookup_value with values in lookup_array. The default value for this argument is 1.
If Match_type is 1, [Match finds the largest value that is less than or equal to lookup_value and it requires sorting the lookup_array in ascending order, i.e. from small to large or from A to Z.]
If Match_type is 0, [Match finds the first value exactly equal to lookup_value and the lookup_array argument can be in any order.]
If match_type is -1 [Match finds the smallest value that is greater than or equal to lookup_value. The lookup_array must be sorted in descending order.]
- Match returns the position of the matched value within lookup_array.
- Match returns the #N/A error if no match is found.
- A match is not case-sensitive.
If the lookup_array contains several occurrences of the lookup_value, the position of the first value will be returned.
We can understand this a little by the following example:-
In the above image, we put all three kind of formula (1,0,-1)
In the 1st formula, lookup_value is “35” but in Column B there is no value appearing as “35”, so in this formula we put match_type “1” because it will return a position of nearest and below of value “35” and the position is 2nd, i.e. below “35”, “34” is the nearest and below “35” value. So the result is “2”
In the 2nd formula, lookup_value is “34” and Column B has the value “34” so we put match_type “0”, and it gives us position of “34” is 2nd. So the result is “2”.
In the 3rd formula, lookup_value is “52” and which exist in Column B but when we put match_type (-1) the result is #N/A, The reason is #N/A is because as per the rule if the match_type “-1” then we have to sort the range in the form of descending order, so once we descending the Column B then see what will be position of “52”
The position of “52” in count column in “1” and now we will learn formula combination with Match function.
Match Function with Vlookup
Following example is the best example to know that what is the use of Match Function with Vlookup.
Cell “G2” has a Vlookup formula which is showing in the upper formula bar.
Here we can see that this is a simple Vlookup formula which returns a “Unit Price”. Now we want to know that if we insert a new column between “Product Name” and “Unit Price” in this table, so will this effect to our result?
Let’s understand a new image :-
Now what we will do just insert a new column between Column A and Column B and see what will happen with the result “Unit Price” see the image below :-
Here we can see that when we insert a new columns between Column A and Column B, the result of “Unit Price” have been change to “Exotic Liquids” form “19” as appearing in above “image 04” and which is not correct. So how to get the correct result.
Now in this situation “Match Function” comes in rescue.
Now we will combibe the the Match Function with Vlookup and then see what happened :-
This image is the same as “image no 04” but in this image we get the result with the help of Vlookup and Match, we can see the formula bar the formula is loaded by “Match Function”. We have seen that there is no change in the result. It is same as we get by the single Vlookup.
Now we will do the same practical as we did in previous image to insert a new column bewtwee Column A and Column B and see what happen :-
Now in this image we can see that we added a column “Supplier” between Column A and Column B. But after addition a new column we can see that there is no change on result, it is as before. So match function does not care about delete or add new column.
Now it is clear that the role of Match Function is to look for the matching figure and in this table Match Function search for Unit Price from the list A1: E 1, and it does not matter that how many column we add or delete. The formula will produce the result accurately without any cell or column change or change in result.
Now this is clear that if Vlookup and Match function will work together, it will produce the dynamic result and one another benefit is that it prevents from error.
I hope this post has helped you understand how the Vlookup and Match function work together and produce a dynamic result.