The Match Function is applied as: =MATCH(104,B2:B8,1) In the first example we have applied a Match function as shown in the above image.Similarly #N/A error also occurs if the defined cell range is not sorted in descending order for ‘match_type’ equal to -1. If the array is not sorted in the ascending order for ‘match_type’ 1 then it results into a #N/A error. Match function does not return the matching string, it only returns the relative position of that string. We will cover this with an example later. Match function also supports the use of wildcard operators, but they can only be used in case of text comparisons where the ‘match_type’ is 0. If the Match formula cannot find any matches, it results into #N/A error. the third parameter of match function is omitted, then the function treats its value to be 1 as default. It does not know the difference between upper and lower case. It can only be applied if the array (‘range’) is sorted in a descending order.įew Important things about Match Formula: If ‘match_type’ has a value -1, it means that match function will find the smallest value that is greater than or equal to ‘lookup_value’.In this case sorting of array (‘range’) is not important. If ‘match_type’ has a value 0, it means that match function will find the first value that is equal to the ‘lookup_value’.It can only be applied if the array (‘range’) is sorted in an ascending order. If ‘match_type’ has a value 1, it means that match function will find a value that is less than or equal to ‘lookup_value’.
‘match_type’ Match type is an important thing. ‘range’ is the array of values on which you want to perform a match. Here: ‘lookup_value’ signifies the value to be searched in the array. Match formula can be written as: MATCH(lookup_value, range, match_type) In simple plain language Match function searches for a value in a defined range and then returns its position. Few Important things about Match Formula:Įxcel defines match function as: “Returns the relative position of an item in an array that matches a specified value in a specified order”.
0 kommentar(er)
