Lookups · eight-software comparison

XLOOKUP exact-match lookup

Compare XLOOKUP in LibreOffice Calc, PlanMaker, ONLYOFFICE, Apple Numbers, and Zoho Sheet; find alternatives for older spreadsheet engines.

Task

Find SKU-103 in the SKU column D2:D6 and return its label from E2:E6. If absent, return “Not found”.

Expected output

Gamma

Compatibility trap: Exact matching is the default, but wildcard, regex and version behaviour differ significantly.

One shared five-row test dataset

The same source rows make results easier to compare. Use the CSV in any supported spreadsheet application; the ODS workbook also includes LibreOffice formula tests.

Sample data (.csv) ↓Calc test workbook (.ods) ↓

Shared formula comparison fixture (columns A through F)
Region (A)	Status (B)	Amount (C)	SKU (D)	Label (E)	Units (F)
North	Open	125	SKU-101	Alpha	2
South	Closed	80	SKU-102	Beta	1
North	Open	75	SKU-103	Gamma	3
North	Closed	50	SKU-104	Delta	4
South	Open	200	SKU-105	Epsilon	1

Formula syntax and feature differences

“Documented” rows cite official product help, but were not run in the destination software. A formula shown here is a migration candidate, not a guarantee of identical behaviour or file interoperability.

LibreOffice Calc

Native tested

Version: Introduced in LibreOffice 24.8.

Arguments: XLOOKUP(search; search_range; return_range; [if_not_found]; [match_mode]; [search_mode])

=XLOOKUP("SKU-103";D2:D6;E2:E6;"Not found")

Exact match when match mode is omitted. Match mode 2 uses wildcards; 3 supports regex.

Alternative: INDEX+MATCH for older Calc releases.

Official documentation ↗

Apache OpenOffice Calc

Alternative

Arguments: INDEX + MATCH

=INDEX(E2:E6;MATCH("SKU-103";D2:D6;0))

Use an explicit exact-match MATCH(…;…;0) to avoid approximate lookup. XLOOKUP parity is not established here.

Alternative: VLOOKUP(...;2;0) is another option.

Official documentation ↗

Gnumeric

Alternative

Arguments: INDEX + MATCH

=INDEX(E2:E6,MATCH("SKU-103",D2:D6,0))

Gnumeric’s published lookup manual documents INDEX and MATCH; XLOOKUP is not claimed.

Alternative: Or VLOOKUP with approximate set to FALSE.

Official documentation ↗

Calligra Sheets

Alternative

Arguments: VLOOKUP(value;source;column;sorted)

=VLOOKUP("SKU-103";D2:E6;2;FALSE)

Use explicit FALSE. Its documented VLOOKUP defaults to sorted/approximate matching.

Alternative: Return column must be to the right of the key.

Official documentation ↗

PlanMaker

Documented

Version: PlanMaker 2026 help.

Arguments: XLOOKUP(search_value; lookup_array; return_array; [if_not_found]; [match_mode]; [search_mode])

=XLOOKUP("SKU-103";D2:D6;E2:E6;"Not found")

PlanMaker 2026 documents XLOOKUP with optional fallback and match modes. Wildcard mode is not evidence of regex mode.

Official documentation ↗

ONLYOFFICE Spreadsheet Editor

Documented

Version: Current documented edition.

Arguments: XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

=XLOOKUP("SKU-103",D2:D6,E2:E6,"Not found")

Exact match is the default; wildcard match mode 2 is documented.

Official documentation ↗

Apple Numbers

Documented

Version: Current Apple Functions guide.

Arguments: XLOOKUP(search-value, search-range, return-range, if-not-found, match-type, search-type)

=XLOOKUP("SKU-103",D2:D6,E2:E6,"Not found")

Numbers has XLOOKUP. The syntax shown assumes D/E are columns in the Numbers table containing this formula.

Official documentation ↗

Zoho Sheet

Documented

Version: Online service; features may change.

Arguments: XLOOKUP(lookup_value;search_table;result_table;[if_not_found];[match_mode];[search_mode])

=XLOOKUP("SKU-103";D2:D6;E2:E6;"Not found")

Zoho documents match mode 2 patterns using regex-like syntax rather than simply treating it as identical to Excel-style wildcards.

Official documentation ↗

What changes when you migrate?

The key transfer trap is interpreting a common name as identical matching rules: LibreOffice documents regex mode 3, while Zoho describes a different pattern vocabulary in mode 2.

For exact lookups, leave match mode at its default only when you have checked that default; otherwise specify exact mode 0 explicitly.

For older engines, INDEX+MATCH permits a return column to the left or right of the search column.

Actual LibreOffice calculation evidence

Formula tested in LibreOffice Calc 25.2.3.2. The published manifest provides the exact input formula, checked result, and whether the test validates a scalar result rather than array spill layout.

Test ID: xlookup. See calculation results (JSON).

Exact match is the easy case; pattern matching is not

In the fixture, the SKU sits in column D and the label in column E. The lookup key SKU-103 has one exact match, so a correctly configured XLOOKUP returns Gamma. This does not test duplicates, case sensitivity, the order of matches, or the different pattern dialects of each engine.

A missing key should return the explicit fallback Not found. This makes the workbook easier to audit than allowing a blank to hide behind formatting. You should also test a first/last-match scenario before migrating business-critical product lists.

An important pattern migration trap

LibreOffice Calc’s published XLOOKUP help includes a regex match mode, while Apple Numbers supports REGEX values when wildcard mode is selected. Zoho Sheet’s documentation demonstrates regex-like lookup patterns. These are not interchangeable recipe strings, even when the functions share the name XLOOKUP.

PlanMaker 2026 and ONLYOFFICE also document XLOOKUP, but the versions installed at your workplace may differ. When a colleague cannot open a newer function, the practical fix is often an explicit INDEX plus MATCH(...;...;0) pattern, not a different file extension.

Migration checklist

Test SKU-103, SKU-999, and a duplicated SKU separately.
Confirm exact mode and search direction; never assume a wildcard pattern travels unchanged.
If opening a workbook in an older spreadsheet app, check whether formulas remain formulas or have been converted to cached values.