XLOOKUP in Collabora Office: Exact, Last and Approximate Matches

• First introduced: not established by the official release-history evidence reviewed. Documentation for 26.04 demonstrates support in that documentation scope; it does not establish the first release.
• Current documented status: supported in Collabora Office Calc 26.04 desktop Help, checked October 13, 2026. No retirement or removal is documented by this source.
• Last supported version: no final version established. “Supported in 26.04” is not a claim that 26.04 is the last working release.
• Platform and edition: this guide follows desktop Calc Help. A Collabora Online server or mobile app may use a different core version or interface. Confirm the installed/deployed build before relying on the examples there.
• Native testing: not performed. The array-entry instructions below are documentation-based.

=XLOOKUP(search_criterion; search_array; result_array; [if_not_found]; [match_mode]; [search_mode])

The labels above are shortened explanatory names for the official arguments. search_array must be one-dimensional and stay on one sheet. The return range must align with the lookup positions: when searching five rows, provide five corresponding return rows.

Square brackets describe optional arguments; do not type them. To skip an argument while supplying a later one, preserve its semicolon position. See the official match and search mode definitions.

Array entry matters in Calc

The 26.04 reference describes returning ranges with array formulas. For a reproducible desktop test, use the array-formula entry workflow: select the intended output cell or output range, enter the formula, and confirm with Ctrl+Shift+Enter. Calc supplies enclosing braces in the formula bar; do not type the braces yourself. Do not assume Excel’s automatic spill and resizing behavior in every Collabora deployment.

For scalar examples, use one output cell. For the two-column return below, reserve two output cells. The published Help is the basis for this workflow; exact behavior still needs testing in the reader’s installed build.

Enter this table in A1:D6, keeping prices numeric. Put the lookup formulas in column G or later.

=XLOOKUP("P200";A2:A6;C2:C6;"Missing";0;1)

Expected result: 7. P200 is the second entry in the lookup range, so the corresponding second price is returned. This is a positional match, not a search for the largest price or an aggregation of several records.

Try changing P200 to P999:

=XLOOKUP("P999";A2:A6;C2:C6;"Missing";0;1)

Expected result: Missing. An explicit fallback is useful for unlisted product codes. Keep it visibly different from a genuine numeric result: replacing missing prices with zero can conceal incomplete data.

=XLOOKUP("P100";A2:A6;C2:C6;"Missing";0;1)
=XLOOKUP("P100";A2:A6;C2:C6;"Missing";0;-1)

Expected results: 12 and 15, respectively. The first formula chooses row 2; the reverse search chooses row 4. Neither formula proves which entry is newest. Reverse search means “last in the current range order,” so sort by an authoritative date before treating the last row as the latest record.

If you need the combined value for all P100 rows, use an aggregation such as SUMIF rather than changing the search direction.

Select G8:H8 before entering this array formula:

=XLOOKUP("P300";A2:A6;B2:C6;"Missing";0;1)

Expected output across G8:H8: Box and 25. The result range spans two columns, so one matched row returns two values. Selecting only one output cell is not a reliable way to inspect a multi-cell result in the documented array-formula workflow.

Enter thresholds 0, 50, 100, 200 in A11:A14 and labels Starter, Standard, Priority, Premium in B11:B14. Then use:

=XLOOKUP(120;A11:A14;B11:B14;"Below all thresholds";-1;1)

Expected result: Priority. There is no exact threshold of 120. The next smaller threshold is 100. This is useful for bands whose lower boundary is inclusive.

Using match mode 1 instead would select the next larger threshold, 200, returning Premium. Test exact boundaries such as 100 and values below the smallest threshold before adopting a banding formula.

• Wrong but plausible results: check range alignment and duplicate keys before investigating formatting.
• Binary search: search modes 2 and -2 require the documented sort order. An unsorted range can give an incorrect answer without a helpful error. Linear search is the clearer default for small operational tables.
• Wildcards and regular expressions: match modes 2 and 3 cannot be combined with binary search. Do not assume a regex written for another spreadsheet has identical behavior.
• Unexpected fallback: compare actual key data types and spaces. A product code imported as text needs consistent handling across both sides of the lookup.
• Blocked or partial multi-cell output: reserve the intended destination and check array entry, rather than copying Excel-specific spill instructions into Calc.

Collabora documents this function in the COM.MICROSOFT.XLOOKUP namespace and states that it is outside OpenFormula 1.3. A file opening successfully in another app does not establish that it can recalculate the formula. For older destinations, consider an explicitly tested INDEX and MATCH construction.

For native validation, record Help → About information, locale, argument separator, entry method, and the four expected outputs above. This separates documentation-based support from a reproducible observation in a particular deployment.

• Collabora Office 26.04: XLOOKUP, checked October 13, 2026.
• Editorial standards.