Difference between revisions of "Documentation/How Tos/Calc: MATCH function"

Revision as of 22:50, 5 May 2008

MATCH

Returns the position of a search item in a single row or column table.

Syntax:

MATCH(searchitem; searchregion; matchtype)

searchitem is the value to be found within the single row or single column range searchregion.

If matchtype is 1 or omitted, searchregion is assumed to be sorted in ascending order. MATCH then returns the position of the largest value in searchregion that is less than or equal to searchitem.

If matchtype is -1 or omitted, searchregion is assumed to be sorted in descending order. MATCH then returns the position of the smallest value in searchregion that is greater than or equal to searchitem.

If matchtype is 0, MATCH returns the position of the first exact match. searchregion may be unsorted. In this case searchitem may contain a regular expression, which will be interpreted if regular expressions are selected in the Tools - Options - OpenOffice.org Calc - Calculate dialog.

Example:

MATCH(5; B1:B4; 1)

where cells B1, B2, B3, B4 contain 3, 4, 8 and 9, returns 2. The second value in B1:B4 is 4, which is the largest value that is less than or equal to 5.

MATCH(5; B1:B4; -1)

where cells B1, B2, B3, B4 contain 9, 8, 4 and 3, returns 2. The second value in B1:B4 is 8, which is the smallest value that is greater than or equal to 5.

MATCH(5; B1:B4; 0)

where cells B1, B2, B3, B4 contain 4, 7, 5 and 2, returns 3, as the value 5 is the third item in B1:B4.

MATCH("r.d"; B1:B4; 0)

where cells B1, B2, B3, B4 contain blue, red, green and pink, returns 2 (if regular expressions are enabled). The regular expression "r.d" matches red.

See also:

CHOOSE, HLOOKUP, INDEX, LOOKUP, OFFSET, VLOOKUP

Regular Expressions in Calc

Spreadsheet functions

Issues:

• MATCH cannot yet accept an array for the second parameter, as required by the forthcoming international standard ODFF (issue 8947). For example MATCH(2;{1;2;3};0) returns Err:504.