Difference between revisions of "Manuals/calci/MATCH"
Jump to navigation
Jump to search
| Line 2: | Line 2: | ||
where, | where, | ||
| − | * | + | *'''lkup_val''' is the value to be matched from the array |
| − | * | + | *'''lkup_arr''' 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. |
MATCH() displays the relative position of a value in an array that matches a specified value in a specified order. | MATCH() displays the relative position of a value in an array that matches a specified value in a specified order. | ||
| Line 18: | Line 18: | ||
*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. |
| − | * | + | *'''lkup_arr''' can be set of values or a reference to cells containing values. |
| − | *If | + | *If '''lkup_val''' and '''lkup_arr''' are text values, they should be entered in quotes (e.g. "JOHN"). |
| − | *The method to find a match can be specified with argument | + | *The method to find a match can be specified with argument '''m_type'''. If omitted, Calci assumes it to be '1'. |
| − | *The following table shows how | + | *The following table shows how '''m_type''' works with respect to the order of values in Array- |
{| class="wikitable" | {| class="wikitable" | ||
|- | |- | ||
! m_type !! Order of values in Array !!Description | ! m_type !! Order of values in Array !!Description | ||
|- | |- | ||
| − | | 1 or omitted || Ascending Order || Finds the largest value <= | + | | 1 or omitted || Ascending Order || Finds the largest value <= '''lkup_val'''. |
|- | |- | ||
| − | | 0 || Any order || Finds the first value exactly equal to | + | | 0 || Any order || Finds the first value exactly equal to '''lkup_val'''. |
|- | |- | ||
| − | | -1 || Descending order || Finds the smallest value >= | + | | -1 || Descending order || Finds the smallest value >= '''lkup_val'''. |
|} | |} | ||
*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 17:21, 14 January 2014
MATCH(lkup_val, lkup_arr, m_type)
where,
- lkup_val is the value to be matched from the array
- lkup_arr 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.
MATCH() displays the relative position of a value in an array that matches a specified value in a specified order.
Description
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.
- 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.
- lkup_arr 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").
- The method to find a match can be specified with argument m_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-
| m_type | Order of values in Array | Description |
|---|---|---|
| 1 or omitted | Ascending Order | Finds the largest value <= lkup_val. |
| 0 | Any order | Finds the first value exactly equal to lkup_val. |
| -1 | Descending order | Finds the smallest value >= lkup_val. |
- 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 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'.