# Documentation/Calc Functions/MATCH

TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate

## Function name:

MATCH

## Category:

Spreadsheet

## Summary:

The function gives the relative position of an item in an array that matches a specified value. The function returns the position of the value found in the lookup_array as a number.

## Syntax:

MATCH(**SearchCriterion**; **LookupArray**[; '* Type'*])

## Returns:

Returns a positive whole number ranging from 1 which is the position of the **SearchCriterion** in the **LookupArray** which can be in ascending or descending order as mentioned by the '* Type'* argument.

## Arguments:

**SearchCriterion** is the value that is to be searched for in the single-row or single-column array. It is entered either directly or as a reference to a cell containing that value.

**LookupArray** is the reference searched. A lookup array can be a single row or column, or part of a single row or column.

**Type** may take the values 1, 0, or -1. If **Type** = 1 or if this optional parameter is missing, it is assumed that the first column of the search array is sorted in ascending order. If **Type** = -1 it is assumed that the column is sorted in descending order. This corresponds to the same function in Microsoft Excel.

If **Type** = 0, only exact matches are found. If the search criterion is found more than once, the function returns the index of the first matching value. Only if **Type** = 0 can you search for regular expressions (if enabled in calculation options) or wildcards (if enabled in calculation options).

If **Type** = 1 or the third parameter is missing, the index of the last value that is smaller or equal to the search criterion is returned. For **Type** = -1, the first value that is larger or equal is returned.

- If
**SearchCriterion**is of type Text and the value found is of type Number, the #N/A Error is returned. - For
**Type**= 0, if no value is equal to**SearchCriterion**exists, the #N/A Error is returned.

## Additional details:

- For Text the comparison is case-insensitive.
- Sorted ascending includes smaller Text values before larger ones (e.g., "A" before "B", and "B" before "BA"), and False before True.
- If the types are mixed, Numbers are sorted before Text, and Text before Logicals. Evaluators without a separate Logical type may include a Logical as a Number.
- The values returned may vary depending upon the HOST-USE-REGULAR-EXPRESSIONS or HOST-USE-WILDCARDS or HOST-SEARCH-CRITERIA-MUST-APPLY-TO-WHOLE-CELL properties. You can know more about it in Host-Defined Behaviors.
- The search supports wildcards or regular expressions. With regular expressions enabled, you can enter "all.*", for example to find the first location of "all" followed by any characters. If you want to search for a text that is also a regular expression, you must either precede every character with a "\" character, or enclose the text into \Q...\E. You can switch the automatic evaluation of wildcards or regular expression on and off in
**Tools - Options - LibreOffice Calc - Calculate**.

## Examples:

A | B | |
---|---|---|

1 | Employee | Salary |

2 | Hans | 21682 |

3 | Brigitte | 25250 |

4 | Ute | 46782 |

5 | Fritz | 54806 |

Formula | Description | Returns |
---|---|---|

=MATCH(25250;B2:B5) | As soon as this value is reached, the number of the row in which it was found is returned. Here B2 will be the first row and result is in B3. | 2 |

=MATCH(50000;B2:B5) | There is no exact match for 50000 so the function looks for a value just smaller than 50000 and the number of the row in which it was found is returned. Here B4's row index will be returned. | 3 |

=MATCH(50000;B2:B5,1) | There is no exact match for 50000 so the function looks for a value just smaller than 50000 and the number of the row in which it was found is returned. Here B4's row index will be returned. Passing Type as 1 indicates that LookupArray is in ascending order. |
3 |

=MATCH(50000;B2:B5,0) | There is no exact match for 50000 and function has Type = 0 so function returns #N/A error. |
#N/A |

=MATCH(25250;B2:B5,-1) | The LookupArray is in ascending order, Type is -1 and but since there is an exact match so the function returns as row index for the found value. |
2 |

=MATCH(50000;B2:B5,-1) | The LookupArray is in ascending order, Type is -1 and there is no exact match hence the function returns an #N/A error. |
#N/A |

=MATCH(50000,{54806;46782;25250;21682},-1) | The LookupArray is in descending order, Type is -1 so the function returns array index of the number just greater than the value 50000. |
1 |

## Related LibreOffice functions:

## ODF standard:

## Equivalent Excel functions:

MATCH