XLOOKUP Function (Gnumeric)
Look up a value in one Gnumeric column or row and return the corresponding item from another range.
Verification status: Official documentation referenced; example outcomes independently reasoned or arithmetically checked where stated. Not executed in Gnumeric. How formula examples are checked
Quick answer
XLOOKUP searches a row or column for a supplied key, then returns the corresponding entry from a separate range. It is a useful alternative to VLOOKUP when the result column appears to the left, when you need last-match search, or when you want a custom not-found response. In the Gnumeric 1.12.62 release-tagged implementation, the exact-match mode is the default. Official source.
=XLOOKUP("Pear",A2:A5,B2:B5)
With the sample rows below, expected result: 8. This is a logical independent selection, not a native Gnumeric output.
Exact Gnumeric syntax
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
| Argument | Required? | Gnumeric 1.12.62 type/meaning | Default |
|---|---|---|---|
lookup_value |
Yes | Text, numeric key or suitable cell value. | None |
lookup_array |
Yes | Row or column range/array in which the key is searched. | None |
return_array |
Yes | Range/array containing values aligned with lookup positions. Gnumeric implementation can return corresponding multiple values. | None |
if_not_found |
No | Value returned if no key matches. | #N/A |
match_mode |
No | 0 exact, -1 exact/next smaller, 1 exact/next larger, 2 wildcard text match. |
0 |
search_mode |
No | 1 start→end, -1 end→start, 2 binary ascending, -2 binary descending. |
1 |
The tagged registry exposes signature EAA|?ff, marks the implementation COMPLETE, NO_TESTSUITE, and permits a nonscalar return. Implementation and registry Registry. Text comparisons are case-folded in the common matching helper (a case-insensitive default is expected), and comparisons exclude incompatible value types; do not promise uniform coercion of text-looking numbers. Matching logic.
English-language formulas above show comma separators; locale settings may alter typing/display. Prefer numeric cells for numeric matches and use the Gnumeric function wizard when pasted syntax is rejected.
Reproducible product-price example
Enter these columns in the indicated cells (row 1 can contain headers Product, Price):
| Row | A: Product | B: Price (number) |
|---|---|---|
| 2 | Apple | 4 |
| 3 | Pear | 8 |
| 4 | Plum | 7 |
| 5 | Pear | 9 |
| Formula | Expected output | Meaning |
|---|---|---|
=XLOOKUP("Pear",A2:A5,B2:B5) |
8 |
First exact Pear match, row 3. |
=XLOOKUP("Pear",A2:A5,B2:B5,"Missing",0,-1) |
9 |
Last Pear match, row 5. |
=XLOOKUP("Kiwi",A2:A5,B2:B5,"Missing") |
Missing |
User-supplied fallback. |
=XLOOKUP("Kiwi",A2:A5,B2:B5) |
#N/A |
No matching key and no fallback. |
=XLOOKUP("P*",A2:A5,B2:B5,"Missing",2) |
8 |
Wildcard matches first key beginning P in linear search. |
All these are source-based expected outputs checked by independently reading the synthetic dataset, not observed native calculations. Gnumeric’s wildcard processing uses the Excel-style wildcard-regular-expression converter when match_mode=2 and the search is linear. Tagged matching source.
Advanced: approximate numeric lookup and reverse search
To assign a bracket to score 28, enter D2:D5 as numbers 10, 20, 30, 40 and E2:E5 as text Low, Medium, High, Very high. Then:
=XLOOKUP(28,D2:D5,E2:E5,"Below minimum",-1)
Expected: Medium, because 20 is the greatest listed value no larger than 28. Mode 1 (next larger) would yield High from 30. The Gnumeric helper picks the best eligible smaller/larger value for linear search even when the set is unsorted; binary modes 2 and -2 assume correct sort order, and unsorted input can give unreliable positions. Source L863–943.
For performance on large, correctly sorted ranges, use binary search mode only after verifying sort order. Do not mix wildcard match mode 2 with binary search modes 2/-2: the Gnumeric tagged implementation explicitly does not handle wildcard matching along its binary-search branch. Source.
In the 1.12.62 source, a horizontal lookup row is supported, and a corresponding multi-column/multi-row result can be returned as an array. That array output may require application-specific display/spill handling; native testing has not established the presentation behavior.
Errors, limits and troubleshooting
| Symptom | Likely cause or response |
|---|---|
#N/A |
Key was not found. Check spelling, leading/trailing whitespace and key types; optionally provide if_not_found. |
#VALUE! |
Bad lookup range shape / pattern validation can return this error in the tagged implementation. Simplify to one row or column. |
#REF! |
Matching position falls beyond the corresponding return range in tagged source; ensure aligned dimensions. |
| Unexpected first match | search_mode=1 defaults to first-to-last. Use -1 to retrieve the last matching row. |
Wrong result with search_mode=2 |
Data is not sorted ascending or sorts as a different type; use linear search or sort it correctly. |
| Wildcard unexpectedly unmatched | Ensure match_mode=2 and linear search; 2 is not a regular-expression mode in Gnumeric 1.12.62. |
| Function not recognized | XLOOKUP introduced in Gnumeric 1.12.61, so check installed Gnumeric/plugins. |
The helper uses case-insensitive text comparison; a case-sensitive lookup should not be assumed without further native validation. Source L831–861.
Official reference and verification
- Gnumeric — function documentation
- Official source
- Registry
- Matching logic
- Tagged matching source
- Source L863–943
- Source
Verification status: Official documentation checked; example outcomes were independently reasoned or arithmetically checked where applicable. Not executed in Gnumeric.
- No genuine Gnumeric screenshots are included; this guide does not use mock application images.