Difference between revisions of "Manuals/calci/MATCH"
Jump to navigation
Jump to search
(Created page with "<div id="6SpaceContent" class="zcontent" align="left"> <font color="#484848"><font face="Arial, sans-serif"><font size="2">'''MATCH'''</font></font></font><font color="#48484...") |
|||
| (8 intermediate revisions by 3 users not shown) | |||
| Line 1: | Line 1: | ||
| − | <div | + | <div style="font-size:30px">'''MATCH (Value,Array,Type)'''</div><br/> |
| − | + | 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 | + | 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- | ||
| + | {| class="wikitable" | ||
| + | |- | ||
| + | ! 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 == | |
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
<div id="2SpaceContent" class="zcontent" align="left"> | <div id="2SpaceContent" class="zcontent" align="left"> | ||
{| id="TABLE3" class="SpreadSheet blue" | {| id="TABLE3" class="SpreadSheet blue" | ||
|- class="even" | |- class="even" | ||
| − | | class=" " | | + | | class="sshl_f" |34 |
| − | | | + | | class=" " |JOHN |
| − | + | | class=" " |45 | |
| − | + | ||
| − | | | ||
| − | |||
|- class="odd" | |- class="odd" | ||
| − | | class=" | + | | class="sshl_f" |46 |
| − | + | | class=" " |SMITH | |
| − | | class=" | + | | class=" " |12 |
| − | | class=" | + | |
| − | |||
|- class="even" | |- class="even" | ||
| − | + | | class="sshl_f" |51 | |
| − | | class="sshl_f | + | | class=" " |MARY |
| − | | class=" | + | | class=" " |3 |
| − | | class=" " | | + | |
| − | |||
|- class="odd" | |- class="odd" | ||
| − | + | | class="sshl_f" |75 | |
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | | class="sshl_f" | | ||
| class=" " | | | class=" " | | ||
| − | | class=" " | | + | | class=" " |1 |
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
|} | |} | ||
| − | < | + | =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'. <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. <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. <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'. <br />Returns the relative position '''3''' for value '3'. | ||
| + | |||
| + | ==Related Videos== | ||
| + | |||
| + | {{#ev:youtube|bJPxV_RopLw|280|center|MATCH}} | ||
| + | |||
| + | == See Also == | ||
| + | |||
| + | *[[Manuals/calci/LOOKUP | LOOKUP]] | ||
| + | *[[Manuals/calci/VLOOKUP | VLOOKUP]] | ||
| + | *[[Manuals/calci/HLOOKUP | HLOOKUP]] | ||
| + | |||
| + | == References == | ||
| + | |||
| + | *[http://en.wikipedia.org/wiki/String_(computer_science) 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 ]] | ||
Latest revision as of 12:57, 23 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
See Also
References