Manuals/calci/MATCH

MATCH(lkup_val, lkup_arr, m_type)

where,

• $lkup_{v}al$ is the value to be matched from the array
• $lkup_{a}rr$ is the array of values or reference to the cells containing values
• $m_{t}ype$ 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_{v}al$ can be a number, text or logical value, or reference to cells containing number, text or logical value.
• $lkup_{a}rr$ can be set of values or a reference to cells containing values.
• If $lkup_{v}al$ and $lkup_{a}rr$ 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 $m_{t}ype$ 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_{v}al$.
0	Any order	Finds the first value exactly equal to $lkup_{v}al$.
-1	Descending order	Finds the smallest value >= $lkup_{v}al$.

• 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'.

See Also

• LOOKUP
• VLOOKUP
• HLOOKUP

References

• String
• Find String