writer
6,694
edits
Changes
no edit summary
=MATCH(lkup_val, lkup_arr, m_type)=
<div style="font-size:30px">'''MATCH (Value,Array,Type)'''</div><br/>
where,
where,
*'''lkup_val''' is the value to be matched from the array
*'''Value''' is the value to be matched from the array
*'''lkup_arr''' is the array of values or reference to the cells containing values
*'''Array''' is the array of values or reference to the cells containing values
*'''m_type''' specifies the method for matching the value with values in array. It can be -1, 0 or 1.
*'''Type''' specifies the method for matching the value with values in array. It can be -1, 0 or 1.
**MATCH(), looks up values in a reference or array.
MATCH() displays the relative position of a value in an array that matches a specified value in a specified order.
== Description ==
== Description ==
MATCH(lkup_val, lkup_arr, m_type)
MATCH (Value,Array,Type)
e.g.
e.g.
MATCH("A",["A","B","C"],1) returns relative position of 'A' as '''1'''.
MATCH("A",["A","B","C"],1) returns relative position of 'A' as '''1'''.
*MATCH() displays the relative position of the matched value in an array.
*MATCH() displays the relative position of the matched value in an array.
*'''lkup_val''' can be a number, text or logical value, or reference to cells containing number, text or logical value.
*'''Value''' can be a number, text or logical value, or reference to cells containing number, text or logical value.
*'''lkup_arr''' can be set of values or a reference to cells containing values.
*'''Array''' can be set of values or a reference to cells containing values.
*If '''lkup_val''' and '''lkup_arr''' are text values, they should be entered in quotes (e.g. "JOHN").
*If '''Value''' and '''Array''' are text values, they should be entered in quotes (e.g. "JOHN").
*The method to find a match can be specified with argument '''m_type'''. If omitted, Calci assumes it to be '1'.
*The method to find a match can be specified with argument '''Type'''. If omitted, Calci assumes it to be '1'.
*The following table shows how '''m_type''' works with respect to the order of values in Array-
*The following table shows how '''Type''' works with respect to the order of values in Array-
{| class="wikitable"
{| class="wikitable"
|-
|-
! m_type !! Order of values in Array !!Description
! Type !! Order of values in Array !!Description
|-
|-
| 1 or omitted || Ascending Order || Finds the largest value <= '''lkup_val'''.
| 1 or omitted || Ascending Order || Finds the largest value <= '''Value'''.
|-
|-
| 0 || Any order || Finds the first value exactly equal to '''lkup_val'''.
| 0 || Any order || Finds the first value exactly equal to '''Value'''.
|-
|-
| -1 || Descending order || Finds the smallest value >= '''lkup_val'''.
| -1 || Descending order || Finds the smallest value >= '''Value'''.
|}
|}
*The uppercase and lowercase characters are not distinguished in an array of text values.
*The uppercase and lowercase characters are not distinguished in an array of text values.
|}
|}
=MATCH(46,A1:A4,1) : Matches value '46' with that in an array with reference cells A1 to A4. Returns the relative position '''2'''.
=MATCH(46,A1:A4,1) : Matches value '46' with that in an array with reference cells A1 to A4. <br />Returns the relative position '''2'''.
=MATCH(39,A1:A4,1) : As no exact match, Calci finds largest number in array that is less than '39'. Returns the relative position '''1''' for value '34'.
=MATCH(39,A1:A4,1) : As no exact match, Calci finds largest number in array that is less than '39'. <br />Returns the relative position '''1''' for value '34'.
=MATCH("MARY",B1:B3,0) : Matches string 'MARY' with that in an array with reference cells B1 to B3. Returns the relative position '''3'''.
=MATCH("MARY",B1:B3,0) : Matches string 'MARY' with that in an array with reference cells B1 to B3. <br />Returns the relative position '''3'''.
=MATCH(45,C1:C4,-1) : Matches value '45' with that in an array with reference cells C1 to C4. Returns the relative position '''1'''.
=MATCH(45,C1:C4,-1) : Matches value '45' with that in an array with reference cells C1 to C4. <br />Returns the relative position '''1'''.
=MATCH(2,C1:C4,-1) : As no exact match, Calci finds smallest number in array that is greater than '2'. Returns the relative position '''3''' for value '3'.
=MATCH(2,C1:C4,-1) : As no exact match, Calci finds smallest number in array that is greater than '2'. <br />Returns the relative position '''3''' for value '3'.
==Related Videos==
{{#ev:youtube|bJPxV_RopLw|280|center|MATCH}}
== See Also ==
== See Also ==
*[http://en.wikipedia.org/wiki/String_(computer_science) String]
*[http://en.wikipedia.org/wiki/String_(computer_science) String]
*[http://en.wikipedia.org/wiki/Comparison_of_programming_languages_(string_functions)#Find Find String]
*[http://en.wikipedia.org/wiki/Comparison_of_programming_languages_(string_functions)#Find Find String]
*[[Z_API_Functions | List of Main Z Functions]]
*[[ Z3 | Z3 home ]]