XMATCH Function (Gnumeric)
Find a 1-based position in a Gnumeric range using exact, approximate, wildcard or reverse lookup.
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
XMATCH returns the one-based position of a matching item within a search range, not the value in another column. It defaults to an exact search from first to last, and can search backward, find nearest smaller/larger values or use wildcard matching in linear mode. Its first two arguments are required. Gnumeric source.
=XMATCH("Pear",A2:A6)
With the sample range below, expected result: 2: Pear is second within A2:A6 (even though its physical row number is 3). This result was independently calculated, not observed running Gnumeric.
Exact syntax, types and defaults
=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])
| Argument | Required? | Purpose | Default |
|---|---|---|---|
lookup_value |
Yes | Number or text key to locate. | None |
lookup_array |
Yes | One-column/one-row range of candidate keys (a single vector is safest). | None |
match_mode |
No | 0 exact; -1 exact or next smaller; 1 exact or next larger; 2 wildcard for text. |
0 |
search_mode |
No | 1 forward; -1 reverse; 2 ascending binary search; -2 descending binary search. |
1 |
The registry signature is EA|ff, implementation status COMPLETE with testing status NO_TESTSUITE; neither implies this guide has been native-tested. Gnumeric’s text matching helper lowercases through casefold before comparing, so the default match is expected to be case-insensitive. It skips values of unsuitable match type. Tagged help/implementation; matching logic.
Commas represent the English example syntax; use the formula editor for locale-specific punctuation. XMATCH expects a lookup range, not a comma-separated text string.
Reproduce a duplicate-sensitive inventory list
Enter these values into cells A2:A6:
| Relative position | Spreadsheet cell | Text value |
|---|---|---|
| 1 | A2 | Apple |
| 2 | A3 | Pear |
| 3 | A4 | Plum |
| 4 | A5 | Pear |
| 5 | A6 | Grape |
| Formula | Expected | Why |
|---|---|---|
=XMATCH("Pear",A2:A6) |
2 |
Default first matching item. |
=XMATCH("Pear",A2:A6,0,-1) |
4 |
Reverse search finds the last Pear. |
=XMATCH("Grape",A2:A6) |
5 |
Fifth position within the selected range. |
=XMATCH("Kiwi",A2:A6) |
#N/A |
Key not found. |
=XMATCH("P*",A2:A6,2) |
2 |
Wildcard pattern matches the first P-prefixed key. |
The examples are expected source-consistent results checked independently against the stated values; none were executed in Gnumeric. The matching implementation supports wildcard patterns in linear search (match_mode=2) and casefolded text comparison. Matching helper.
Advanced: score thresholds and relative positions
Put numeric values 10, 20, 30, 40 in D2:D5. Then:
=XMATCH(28,D2:D5,-1)
Expected: 2, because 20 is the greatest candidate smaller than 28; use match_mode=1 for expected 3 (the position of 30). When the range has no eligible smaller/larger match and no exact match, the function returns #N/A. This mirrors the tagged helper’s linear approximate lookup selection. Source.
For a warehouse stock list, the position returned by XMATCH can also be used by an analyst to identify the matching row within the supplied search range. Do not confuse the one-based relative index with a physical worksheet row. Combining it with other formulas is useful, but any combined examples require their own application validation.
Sorted binary search versus wildcard mode
search_mode=2 and -2 require input sorted in ascending or descending order. Unsorted data can return unexpected matches, so a simple forward search (1) is often safer. The Gnumeric tagged source’s binary branch explicitly returns no match for wildcard mode 2, instead of executing a wildcard search there. Do not combine wildcard mode with binary search in Gnumeric 1.12.62. Source L863–895.
Errors, limitations and fixes
| Symptom | Suggested check |
|---|---|
#N/A |
No matching key/threshold; inspect range and data types. |
#VALUE! |
Malformed lookup array or invalid wildcard pattern can cause the matching helper to signal a data error. |
| Position seems off by one | XMATCH returns 1 for the first cell of the lookup range, not its worksheet row/column number. |
| Duplicate gave first match | Set the fourth argument search_mode=-1 with match_mode=0 for last-first search. |
| Wrong binary result | Verify ascending/descending sort and consistent numerical/text types, or use search mode 1. |
| Wildcard not matching | Use a string key with */? and match_mode=2, with linear search_mode=1 or -1. |
| #NAME? | Gnumeric 1.12.61 introduced XMATCH; confirm version and plugins. |
Official reference and verification
- Gnumeric — function documentation
- Gnumeric source
- matching logic
- Matching helper
- Source
- Source L863–895
- Gnumeric 1.12.61 release announcement
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.