Difference between revisions of "Manuals/calci/MATCH"

From ZCubes Wiki
Jump to navigation Jump to search
Line 1: Line 1:
−
=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() displays the relative position of a value in an array that matches a specified value in a specified order.
+
MATCH(), looks up values in a reference or array.
  
 
== 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'''.
Line 18: Line 17:
  
 
*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 &lt;= '''lkup_val'''.
+
| 1 or omitted || Ascending Order || Finds the largest value &lt;= '''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 &gt;= '''lkup_val'''.
+
| -1 || Descending order || Finds the smallest value &gt;= '''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.

Revision as of 14:35, 2 August 2018

MATCH (Value,Array,Type)


where,

  • Value is the value to be matched from the array
  • Array is the array of values or reference to the cells containing values
  • 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.

Description

MATCH (Value,Array,Type) e.g. MATCH("A",["A","B","C"],1) returns relative position of 'A' as 1.

MATCH(44,[40,42,44],1) returns relative position of '44' as 3.

  • MATCH() displays the relative position of the matched value in an array.
  • Value can be a number, text or logical value, or reference to cells containing number, text or logical value.
  • Array can be set of values or a reference to cells containing values.
  • 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 Type. If omitted, Calci assumes it to be '1'.
  • The following table shows how Type works with respect to the order of values in Array-
Type Order of values in Array Description
1 or omitted Ascending Order Finds the largest value <= Value.
0 Any order Finds the first value exactly equal to Value.
-1 Descending order Finds the smallest value >= Value.
  • The uppercase and lowercase characters are not distinguished in an array of text values.
  • If no matching value is found, Calci displays #NULL error message.
  • For invalid arguments, Calci displays #NULL error message.

Examples

34 JOHN 45
46 SMITH 12
51 MARY 3
75 1 
=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(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("MARY",B1:B3,0) : Matches string 'MARY' with that in an array with reference cells B1 to B3. 
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(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'.

Related Videos

MATCH

See Also

References


Retrieved from "http://wiki.zcubes.com/index.php?title=Manuals/calci/MATCH&oldid=210867"