XMATCH Function (LibreOffice Calc)

• Introduced: LibreOffice 24.8, explicitly stated in function-specific Help.
• Supported in: 26.8 Help and 26.8.1.1 registration; no retirement is established.
• Scope: Desktop Calc, English semicolon-separated formulas. The documented COM.MICROSOFT.XMATCH namespace is outside OpenFormula 1.3.
• Checked: October 14, 2026. Ten listed examples were recalculated in LibreOfficeDev 26.8.0.0.alpha0 using headless XLSX import. Stable releases, 26.8.1.1 and GUI array entry were not executed.

XMATCH returns a one-based relative position inside a single row or column. It does not return the matching cell’s value or its absolute worksheet row. Forward exact matching is the default. Separate match and search modes control what qualifies as a match and which direction or algorithm is used. Function Help.

XMATCH(Search_criterion; Search_array [; Match_mode [; Search_mode]])

Square brackets mark optional arguments; do not type them. Search_criterion is the value or text pattern. Search_array is one row or one column. Match_mode defaults to 0 for exact; −1 selects an exact match or next smaller value, 1 exact or next larger, 2 wildcard matching, and 3 regular-expression matching. Search_mode defaults to 1 for forward search; −1 searches backward, 2 requires ascending binary-search order, and −2 requires descending order. Wildcard and regex modes cannot be combined with binary search.

Enter 10, 20, 20, 40, 60, 80 down H19:H24. Enter 80, 60, 40, 20, 20, 10 down H26:H31. Put alpha, beta, ALPHA, alpaca down L19:L22. For the horizontal example, put A, B, C, D across H15:K15.

The native column is from LibreOfficeDev 26.8.0.0.alpha0, build 2c87e51eeaa2b413ff4ae097b2705eea1995d8e5, using headless XLSX import and forced recalculation. Decimal values are rounded to twelve places; quoted text excludes the surrounding quotation marks from the returned value.

The two occurrences of 20 in H19:H24 occupy relative positions 2 and 3. =XMATCH(20;H19:H24) returns 2, while =XMATCH(20;H19:H24;0;-1) returns 3. These are positions within the range beginning at H19. The returned 2 is not worksheet row 2 or row 20. To obtain the matched value, combine that position with an appropriate INDEX call rather than interpreting the position as data.

Searching for 50 with Match_mode −1 chooses the next smaller value 40 at position 4; Match_mode 1 chooses 60 at position 5. The binary examples deliberately use correctly sorted ranges and search for the unique value 60. A wildcard “alp*” finds the first matching text at position 1, or the final matching text “alpaca” at position 4 with reverse search. The anchored regex “^b.*” selects “beta” at position 2. These examples separate regex syntax from wildcard syntax rather than assuming the two pattern languages are interchangeable. Inspected callback.

Binary search requires the declared sort order; unsorted data can produce invalid results without an obvious error. The inspected search helper explicitly rejects wildcard or regex matching combined with binary modes. An exact search with no match produces #N/A according to the Help, but the listed native cases all have valid matches. Do not use a two-dimensional rectangle as Search_array. Blank, numeric-text and locale-specific matching rules need separate checks; the executed patterns are ordinary ASCII text.

MATCH, XLOOKUP, INDEX.

• Function-specific Help
• Complete versioned callback
• English native-name registration
• Match modes and binary-pattern restriction

The saved formula identities, local input values and typed results were reconciled. The stale SUM cache changed to 3 and the uncached SUM returned 7. Saved formulas use COM.MICROSOFT.XMATCH for this operation. The examples establish only the listed development-build results; unexecuted guards and current-source behavior remain separately scoped.