writer
2,661
edits
Changes
Jump to navigation
Jump to search
Line 1:
Line 1: − <div id="6SpaceContent" class="zcontent" align="left"> <font color="#484848"><font face="Arial, sans-serif"><font size="2">'''MATCH'''</font></font></font><font color="#484848"><font face="Arial, sans-serif"><font size="2">(</font></font></font><font color="#484848"><font face="Arial, sans-serif"><font size="2">'''lkup_val'''</font></font></font><font color="#484848"><font face="Arial, sans-serif"><font size="2">,</font></font></font><font color="#484848"><font face="Arial, sans-serif"><font size="2">'''lkup_arr'''</font></font></font><font color="#484848"><font face="Arial, sans-serif"><font size="2">,m_type)</font></font></font>+ − <font color="#484848"><font face="Arial, sans-serif"><font size="2">'''where lkup_val'''</font></font></font><font color="#484848"><font face="Arial, sans-serif"><font size="2"> is the value which is use to find the value ,</font></font></font><font color="#484848"><font face="Arial, sans-serif"><font size="2">'''lkup_arr '''</font></font></font><font color="#484848"><font face="Arial, sans-serif"><font size="2">is a contiguous range of cells containing possible lookup values and </font></font></font>+ + + + − <font color="#484848"><font face="Arial, sans-serif"><font size="2">'''m_type'''</font></font></font><font color="#484848"><font face="Arial, sans-serif"><font size="2"> is the number -1, 0, or 1. </font></font></font>+ − </div>+ − ---- − <div id="1SpaceContent" class="zcontent" align="left"> <font color="#484848"><font face="Arial, sans-serif"><font size="2">It gives the relative position of an item in an</font></font></font><font color="#000000"><font face="Arial, sans-serif"><font size="2"> </font></font></font>[javascript:AppendPopup(this,'xldefArray_1') <font color="#000000"><font face="Arial, sans-serif"><font size="2">array </font></font></font>]<font color="#484848"><font face="Arial, sans-serif"><font size="2">that matches a specified value in a specified order. </font></font></font></div> − ---- − <div id="12SpaceContent" class="zcontent" align="left"><div class="ZEditBox" align="left"> − + − </div></div>+ − ----+ − <div id="8SpaceContent" class="zcontent" align="left"> − <font color="#484848"><font face="Arial, sans-serif"><font size="2">Let's see an example in Column1Row1:Column1Row4</font></font></font>+ − + − + − + − + − <font color="#484848"><font face="Arial, sans-serif"><font size="2">Blue 47</font></font></font>+ + + + + + + + + + + + + + + − <font color="#484848"><font face="Arial, sans-serif"><font size="2">Yellow 26</font></font></font>+ − <br /><br /> − − <font color="#484848"><font face="Arial, sans-serif"><font size="2">I.e.=MATCH(40,C1R1:C1R4,1) is 4</font></font></font> − − <br /> − − </div> − ---- − <div id="10SpaceContent" class="zcontent" align="left"><div class="ZEditBox" align="justify">Syntax </div><div class="ZEditBox"><center></center></div></div> − ---- − <div id="3SpaceContent" class="zcontent" align="left"><div class="ZEditBox" align="justify">Examples </div></div> − ---- − <div id="11SpaceContent" class="zcontent" align="left"><div class="ZEditBox" align="justify">Description </div></div> − ---- − + − + − |+ − <div id="2Space_Handle" title="Click and Drag to resize CALCI Column/Row/Cell. It is EZ!"></div><div id="2Space_Copy" title="Click and Drag over to AutoFill other cells."></div>Column2+ − − | Column4 − + − | class=" sshl_f " | Green+ − + − + − | class=" " | − | class=" " | Row2+ − + − + − + − | class=" " | − | Row3+ − | class="sshl_f" | Blue − − + − |- class="even" − | Row4 − | class=" " | Yellow − | class="sshl_f" | 26 − | class=" " | − | class=" " | − |- class="odd" − | class=" " | Row5 − | class=" " | − | class="sshl_f" | 4 − | class=" " | − | class=" " | − |- class="even" − | Row6 − | class=" " | − | class="SelectTd " | − | class=" " | − | class=" SelectTD SelectTD" | − <div id="2Space_Handle" title="Click and Drag to resize CALCI Column/Row/Cell. It is EZ!"></div><div id="2Space_Copy" title="Click and Drag over to AutoFill other cells."></div> − <div align="left">[[Image:calci1.gif]]</div></div>+ − ----+ + + + + + + + + + + + + + +
no edit summary
=MATCH(lkup_val, lkup_arr, m_type)=
where,
*<math>lkup_val</math> is the value to be matched from the array
*<math>lkup_arr</math> is the array of values or reference to the cells containing values
*<math>m_type</math> 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.
== Description ==
MATCH
MATCH(lkup_val, lkup_arr, m_type)
e.g.
MATCH("A",{"A","B","C"},1) returns relative position of 'A' as '''3'''.
MATCH(44,{40,42,44},1) returns relative position of '44' as '''3'''.
<font color="#484848"><font face="Arial, sans-serif"><font size="2">Green 45</font></font></font>
*MATCH() displays the relative position of the matched value in an array.
*<math>lkup_val</math> can be a number, text or logical value, or reference to cells containing number, text or logical value.
<font color="#484848"><font face="Arial, sans-serif"><font size="2">Red 39</font></font></font>
*<math>lkup_arr</math> can be set of values or a reference to cells containing values.
*If <math>lkup_val</math> and <math>lkup_arr</math> are text values, they should be entered in quotes (e.g. "JOHN").
*The method to find a match can be specified with argument math>m_type</math>. If omitted, Calci assumes it to be '1'.
*The following table shows how <math>m_type</math> works with respect to the order of values in Array-
{| class="wikitable"
|-
! m_type !! Order of values in Array !!Description
|-
| 1 or omitted || Ascending Order || Finds the largest value <= <math>lkup_val</math>.
|-
| 0 || Any order || Finds the first value exactly equal to <math>lkup_val</math>.
|-
| -1 || Descending order || Finds the smallest value >= <math>lkup_val</math>.
|}
*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
| Column1
| class=" " |JOHN
| class=" " |45
| Column3
|- class="odd"
|- class="odd"
| class=" " | Row1
| class="sshl_f" |46
| class=" " |SMITH
| class="sshl_f" | 45
| class=" " |12
| class=" " |
|- class="even"
|- class="even"
| class="sshl_f" |51
| class="sshl_f " | Red
| class=" " |MARY
| class="sshl_f" | 39
| class=" " |3
| class=" " |
|- class="odd"
|- class="odd"
| class="sshl_f" |75
| class="sshl_f" | 47
| class=" " |
| class=" " |
| class=" " |
| class=" " |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 there is no exact match, Calci finds the largest number in array from A1 to A4, 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 there is no exact match, Calci finds the smallest number in array from c1 to c4, greater than '2'. Returns the relative position '''3''' for value '3'.
== 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]