Manuals/calci/MATCH
Jump to navigation
Jump to search
MATCH(lkup_val, lkup_arr, m_type)
where,
- is the value to be matched from the array
- is the array of values or reference to the cells containing values
- specifies the method for matching the value with values in array. It can be -1, 0 or 1.
is the value to be matched from the array
is the array of values or reference to the cells containing values
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.
- can be a number, text or logical value, or reference to cells containing number, text or logical value.
- can be set of values or a reference to cells containing values.
- If and 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 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 <= . |
| 0 | Any order | Finds the first value exactly equal to . |
| -1 | Descending order | Finds the smallest 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 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'.