Collabora Office XMATCH: Range Positions and Search Modes
Find relative positions in worksheet ranges with exact, reverse and approximate modes, while preserving source limitations for matrix inputs. Scoped to the new Linux desktop source build.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
XMATCH returns a matching item’s relative one-based position in a one-dimensional search range. It does not return the matched value or the worksheet row number. The worked examples here use explicit worksheet ranges because the pinned source has important differences between range and matrix-expression search paths.
Syntax and modes
=XMATCH(Search criterion; Search Array [; Match Mode [; Search Mode]])
Match Mode defaults to 0:
| Match mode | Meaning |
|---|---|
| 0 | Exact match; no match gives #N/A in the help |
| -1 | Exact, otherwise next smaller item |
| 1 | Exact, otherwise next larger item |
| 2 | Wildcard matching |
| 3 | Regular-expression matching |
Search Mode defaults to 1 for forward search. -1 searches backward; 2 is binary search on ascending data; -2 is binary search on descending data. Pattern modes 2 and 3 cannot be combined with binary search modes 2 or -2.
Sortedness is a precondition for binary search, not a request to sort the range. Invalid ordering can produce an invalid result instead of a helpful error. The examples use explicit modes and small numeric ranges rather than inferring text collation or broad pattern compatibility.
Step by step: first and last exact matches
Enter A2:A5 as 10,20,20,40. Then:
=XMATCH(20;A2:A5)
The source/documentation-derived expected result is 2, the first 20’s position relative to A2. The worksheet row of that cell is 3, which is a different coordinate.
=XMATCH(20;A2:A5;0;-1) searches backward and has expected result 3, the last 20’s relative position. It does not return 20, and reverse search does not automatically mean “latest” unless the source range was deliberately ordered by an authoritative time field.
Next smaller and next larger
Enter D2:D4 as 10,20,40, with no duplicates. For a target of 30:
| Formula | Expected position | Selected value |
|---|---|---|
=XMATCH(30;D2:D4;-1;1) |
2 | 20 |
=XMATCH(30;D2:D4;1;1) |
3 | 40 |
These are directional alternatives, not “closest by absolute distance” unless the problem happens to make them equivalent. With default exact mode, =XMATCH(30;D2:D4) has no match and reaches the source’s NotAvailable result.
The numeric range controls are independently traceable through the query iterator. They do not validate every mixed-type, blank or unsorted approximate-match case.
Horizontal and sorted binary controls
Enter F2:H2 as 10,20,40. =XMATCH(40;F2:H2) has expected position 3, now counting across columns.
For ascending D2:D4, =XMATCH(40;D2:D4;0;2) has expected position 3. For descending J2:J4 containing 40,20,10, =XMATCH(20;J2:J4;0;-2) has expected position 2. The keys are distinct so these examples do not imply a particular duplicate choice in binary mode.
No timing comparison was performed. The help’s performance language is not a benchmark of these formulas or a promise that binary search is always faster in a particular workbook.
Important source limitations for array inputs
The wrapper accepts both ranges and matrix values, but they are routed through different helpers. In this pinned commit:
- The matrix forward/reverse match switches lack the regex branch present in the general mode selection, so matrix regex mode can reach internal IllegalParameter.
- The matrix reverse-approximation loop skips the final element because of its starting index and post-decrement condition.
- The matrix binary helper derives ordering from the comparison operator rather than directly from ascending/descending search mode.
These are source findings, not native bug reproductions. Do not substitute an array-producing expression for a worksheet range and assume identical behavior for all modes. The numerical results in this guide deliberately retain their explicit worksheet-range inputs. Regex, matrix binary search and reverse approximate matrix behavior are not certified by them.
Advanced: keep positions aligned with return data
A position such as 2 is meaningful relative to the exact search range. If another column supplies the corresponding amount or label, it must have the same record order and compatible length. Sorting or filtering only one side can attach the correct position to the wrong record.
Choose exact mode when approximate substitutions would be misleading. For threshold tables, document whether the next smaller or next larger threshold is intended and verify endpoint behavior separately. A successful approximate lookup does not prove the selected threshold is appropriate for the underlying decision.
Errors and scope
A two-dimensional worksheet range such as L2:M3 is rejected by the wrapper with internal IllegalParameter. Search Mode 0 is also rejected. Combining wildcard Match Mode 2 with binary Search Mode 2 reaches internal NoValue before the search; the prohibition is independent of whether any text would match.
The guide’s internal error names are source identities, while #N/A is also documented for missing exact matches. No localized error screenshots, actual workbook execution or cross-product equivalence is claimed. Documented optional arguments should not be extended into unverified coercion assumptions for every empty slot.
Product scope and evidence
This guide concerns the new Collabora Office desktop for Linux, source tag coda-26.04.4.3-1, commit 81339d176148d0d5c81ff05d82540f58f8aaa117. The 26.04 XMATCH help and exact evaluator source were checked October 18, 2026. The exact query iterator was also reviewed for the worksheet-range path. The Linux package manifest supplies the recorded product mapping; its mutable publication is not an installed-binary attestation.
No Collabora runtime or spreadsheet calculation engine was executed. Examples are source-derived expectations and independent mathematical controls. No screenshots or interface tests are claimed. This evidence does not establish Classic, Online, CODE, mobile, Windows or macOS parity.
The first Collabora release introducing this function is unknown from the checked evidence. Current documentation and registration do not establish retirement or deprecation, and upstream release history is not substituted for product-specific introduction. Import/export behavior in other products was not tested.