Browse

MATCH Function (LibreOffice Calc)

Lookup Intermediate LibreOffice Calc

Learn the MATCH function in LibreOffice Calc: Defines a position in an array after comparing values.

The MATCH function in LibreOffice Calc is used for this task: Defines a position in an array after comparing values. Find the position of an exact match in a list.

This guide explains how to read the formula, reproduce a concrete example, interpret its result and recognize common input mistakes.

Tested visual example: Find a product position

What this example answers: Locate an exact SKU without relying on sorted data. The formula was entered and recalculated in LibreOffice Calc 25.2.3.2, not simulated or inferred from a reference page.

Sample sheet you can reproduce

Create a sheet named Example, put these headings on row 4, and enter the sample values in rows 5–10. Or download the editable MATCH practice workbook with the formulas already entered.

Sheet row A: SKU B: Product C: Price D: Stock
5 P-101 Pen 2.50 50
6 P-102 Notebook 6.25 40
7 P-103 Folder 4.75 30
8 P-104 Mouse 24.50 12
9 P-105 Cable 9.95 22
10 P-106 Stand 18 9

Example 1 — Formula and expected result

Select cell E5, enter the formula and press Enter:

=MATCH("P-104";A5:A10;0)

Verified result: 4.

MATCH returns a one-based position within the lookup range, not the row number on the worksheet.

Actual LibreOffice Calc 25.2 window showing MATCH formula in the formula bar, example input rows and the calculated result in cell E5

Genuine screenshot from LibreOffice Calc 25.2.3.2: the formula bar, input table and computed output are visible. No interface imagery was generated.

Example 2 — Change the inputs

The same downloadable .ods file has this second formula in E12:

=MATCH("P-102";A5:A10;0)

Verified result: 2. Search for the second SKU instead. The result is its position within A5:A10 (2), not spreadsheet row 6.

How to avoid misleading results

  • Watch for this mistake: Set the third argument to 0 for an exact match; approximate matching uses different sorting rules.
  • Scope and limitations: Use INDEX with MATCH when you need to return a value from another column.
  • Argument separators: These examples use semicolons (;) in the LibreOffice Calc locale tested. Other locales may use different settings.
  • Verification scope: The two formulas in this section were executed and checked in LibreOffice Calc 25.2.3.2. Older examples elsewhere in this article have not all been independently retested.

Official function reference: LibreOffice Help — MATCH.

Syntax and arguments

MATCH(search_criterion; lookup_array; [type])
Argument Required? Meaning
Search criterion Yes The value to be used for comparison.
Lookup array Yes The array (range) in which the search is made.
Type Optional Type can take the value 1 (first column array ascending), 0 (exact match or wildcard or regular expression match) or -1 (first column array descending) and determines the criteria to be used for comparison purposes.

Calc formulas normally use semicolons (;) between arguments in common configurations; the displayed separator may depend on settings or locale.

Worked example

Use this small practice sheet for the ranges referenced in the formula:

Row A (Quantity) B (Amount) C (Category)
1 1 10 apple
2 2 20 pear
3 3 30 apple
4 4 40 pear
5 5 50 apple 
=MATCH(30;B1:B5;0)

Expected result: 3.

Why it works: Find the position of an exact match in a list. The specified sample table supplies the referenced cells.

How to use it in your own spreadsheet

  1. Identify the cells or inputs that correspond to the sample values above.
  2. Enter the formula in an empty cell, changing the references or literal values to match your sheet.
  3. Compare the output with a manual check on a small known example.
  4. Extend the formula to a larger dataset only after you understand the result.

Common problems and practical checks

  • Use the correct lookup column or return range, and use exact-match options where appropriate.
  • Check for duplicate keys, leading spaces and numbers stored as text in lookup data.
  • A pasted formula may produce an error when it uses the other program’s argument separator or unsupported options.

Lookup tip: Use exact matching for product codes or identifiers unless you have deliberately sorted your data for approximate matching. Check the behavior when no match exists.

VLOOKUP HLOOKUP INDEX LOOKUP XLOOKUP.

Documentation and verification

  • LibreOffice Calc Help
  • The standalone example was calculated using LibreOffice Calc 25.2.3.2 on October 9, 2026 and its computed result matched the expected value. This does not certify every option, version, or other example.