XLOOKUP exact-match lookup
Compare XLOOKUP in LibreOffice Calc, PlanMaker, ONLYOFFICE, Apple Numbers, and Zoho Sheet; find alternatives for older spreadsheet engines.
Expected: GammaCompare formulas →
Cross-software formula compatibility
Cross-Software Formula Compatibility Library
Compare real spreadsheet formula examples, arguments, supported alternatives, and version-specific limitations across eight software programs.
A formula that looks identical in two applications may handle pattern matching, empty cells, array results or file export very differently. This section compares specific reproducible tasks, using the same sample data and the official help for each spreadsheet program.
Choose a worked comparison below or use the Formula Migration Helper to choose a source and destination program. The helper shows documented migration candidates; it does not run your workbook or guarantee XLSX/ODS interoperability.
If you only need to know whether our site has published a function guide, use the full function coverage directory. A missing guide here never proves a feature is unsupported in your software.
How to interpret the evidence
- Native tested: The cited historical Calc calculation was actually run in LibreOffice 25.2.3.2 and has downloadable results.
- Documented: Function semantics or syntax were checked against an official vendor reference, but not executed in that destination program.
- Alternative: A different formula solves a similar task; execution and full equivalence are not assumed.
- Not researched: Evidence is insufficient to recommend a particular formula. This does not mean the product lacks support.
The library deliberately uses version notes and transparent testing labels instead of unsupported-feature badges. Recalculate any financial, scientific or production workbook in the target application before relying on an imported formula.
VLOOKUP exact-match portability
Why VLOOKUP can return the wrong result when the final approximate-match argument is omitted, with exact-match syntax for eight spreadsheet tools.
Expected: GammaCompare formulas →
COUNTIFS across spreadsheet programs
Count rows satisfying two conditions, compare COUNTIFS argument patterns, and identify a documented fallback for Calligra Sheets.
Expected: 2Compare formulas →
INDEX + MATCH fallback
Use INDEX and exact-match MATCH to migrate a SKU lookup from XLOOKUP without approximate-match surprises.
Expected: GammaCompare formulas →
Extract digits using regex
Extract the digits 0042 from text without dropping leading zeros, contrasting LibreOffice REGEX, Calligra REGEXP and Apple Numbers REGEX.EXTRACT.
Expected: 0042 (text, leading zeros preserved)Compare formulas →
Replace every numeric run
Replace digits with ID in a text string: compare LibreOffice REGEX global flag and Calligra REGEXPRE behaviour.
Expected: Order IDCompare formulas →
SUMPRODUCT weighted totals
Multiply price by units for each row then sum the products; document array shape and syntax compatibility.
Expected: 955Compare formulas →
FILTER with two conditions
Compare dynamic FILTER arrays, two-condition logic, if-empty behaviour and legacy calculation workarounds across spreadsheet software.
Expected: 125, 75 (two-row result)Compare formulas →
SORT numeric values ascending
Compare SORT argument order and default sort direction: Gnumeric SORT uses a different signature and reverses the default direction.
Expected: 50, 75, 80, 125, 200Compare formulas →
UNIQUE distinct region values
Compare distinct-versus-exactly-once handling and array output when deduplicating region values.
Expected: North, South (two distinct values)Compare formulas →
SUMIFS with two conditions
Add values only when two columns match: compare SUMIFS, criteria modes, documented syntax and older-engine alternatives.
Expected: 200Compare formulas →
AVERAGEIFS for matching rows
Find the mean only for records that match multiple conditions, with notes on divide-by-zero errors and safe alternatives.
Expected: 100Compare formulas →
IFERROR versus IFNA
Compare catch-all error fallbacks with lookup-only #N/A fallbacks without hiding genuine calculation problems.
Expected: Check divisorCompare formulas →
TEXTJOIN with a delimiter and blanks
Join labels with separators, avoid accidental extra delimiters, and compare legacy & concatenation alternatives.
Expected: Alpha / Beta / Gamma / Delta / EpsilonCompare formulas →
TRIM leading spaces without damaging identifiers
Clean ordinary leading/trailing spaces, understand limits around repeated internal spaces and nonbreaking-space characters.
Expected: AlphaCompare formulas →
No comparisons match these filters. Try a different function name or category.
How we evaluate compatibility
Native tested
Executed in LibreOffice Calc 25.2.3.2 against the downloadable sample and checked for calculation errors.
Documented
Feature or syntax checked against the software vendor's documentation, but not executed here.
Alternative
A task-specific workaround or different function, clearly distinguished from direct feature parity.
Not researched
No sufficient source checked. This never means the feature is absent or unsupported.
Source references and version notes appear on each guide. Locale-specific argument separators and imported workbook behaviour may vary; test business-critical files in the destination application.
Research checked: 2026-10-11.