Lookups · eight-software comparison

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.

Task

Look up SKU-103 in D2:E6 and return the second column (label).

Expected output

Gamma

Compatibility trap: On many engines the omitted last argument means approximate matching; an unsorted lookup column can silently return the wrong item.

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: Verified in Calc 25.2.3.2.

Arguments: VLOOKUP(search; range; index; sorted)

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

0/FALSE requests exact match. The default is not a portable substitute for exact mode.

Official documentation ↗

Apache OpenOffice Calc

Documented

Arguments: VLOOKUP(search;range;column;sorted)

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

Set sorted to 0; the search key belongs in the leftmost table column.

Official documentation ↗

Gnumeric

Documented

Arguments: VLOOKUP(value,range,column,approximate,as_index)

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

Approximate defaults TRUE. The optional as_index argument has a Gnumeric-specific return mode.

Official documentation ↗

Calligra Sheets

Documented

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

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

KDE handbook says sorted defaults TRUE; explicitly set FALSE for exact matching.

Official documentation ↗

PlanMaker

Alternative

Version: PlanMaker 2026.

Arguments: VLOOKUP(lookup;table;column;sorted)

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

Exact VLOOKUP is a practical fallback when XLOOKUP is unnecessary. The exact syntax in PlanMaker was not separately executed.

Official documentation ↗

ONLYOFFICE Spreadsheet Editor

Documented

Arguments: VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

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

Use FALSE for exact rather than relying on the default.

Official documentation ↗

Apple Numbers

Alternative

Arguments: XLOOKUP instead of VLOOKUP

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

Use Numbers’ documented XLOOKUP for exact matching; VLOOKUP behaviour was not separately verified in this patch.

Official documentation ↗

Zoho Sheet

Alternative

Arguments: XLOOKUP instead of VLOOKUP

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

Zoho’s XLOOKUP avoids the ambiguity of the VLOOKUP sorted argument.

Official documentation ↗

What changes when you migrate?

For portability, writing FALSE or 0 is more important than shortening the formula by one argument.

Exact VLOOKUP still requires the key to be in the leftmost column of the selected block. Use XLOOKUP or INDEX+MATCH when the return column sits on the left.

Include an out-of-table missing-key test; a plausible-looking result from approximate mode can be more dangerous than an error.

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: vlookup. See calculation results (JSON).

The hidden final argument

Many spreadsheets accept VLOOKUP(key,table,return_column) but interpret the missing final argument as an approximate search. Approximate search can return a plausible-looking, incorrect row when keys are unsorted. The source SKU list is not an ordered numeric lookup table, so exact mode is the intended behaviour.

D2:E6 places the key in the leftmost column. The index 2 means return the second column of that two-column range, not the second column of the entire sheet. FALSE or 0 explicitly requests an exact match in the documented implementations here.

When VLOOKUP is the wrong tool

A return column to the left of the key is awkward with classic VLOOKUP. XLOOKUP and INDEX+MATCH avoid that limitation without rearranging the workbook. Gnumeric also has a fifth, product-specific as_index option; do not simply transfer that argument to another application.

Verify more than the happy path

Use a missing SKU and a duplicate SKU to confirm errors, fallback text and first-match handling. Never take “the workbook opened successfully” as evidence that the formula still calculates to the right row.