XMATCH Function in Google Sheets: Exact, Reverse and Approximate Matches

• First introduced: Google announced XMATCH on August 24, 2022, in its new-functions release; the extended rollout was expected to finish by September 26, 2022. Those are rollout dates, not a numbered desktop version. Google announcement
• Current status: documented in Google Sheets’ current function help. The launch covered Workspace and personal Google Accounts. This guide uses the web editor; it does not verify every mobile authoring workflow.
• Last supported version: no retirement endpoint established; Sheets is a rolling service.
• Evidence checked: October 14, 2026. Current XMATCH reference. Expected positions were independently calculated; no native Sheets execution was performed.

=XMATCH(search_key, lookup_range, [match_mode], [search_mode])

lookup_range must be one row or one column. match_mode is 0 for exact matching by default, 1 for exact or next greater, -1 for exact or next smaller, and 2 for wildcard matching. search_mode defaults to 1 (first to last); -1 searches last to first. Binary modes 2 and -2 require ascending and descending sorting respectively. Google’s argument reference

These examples use English function names and comma argument separators. Adapt separators to the spreadsheet locale where necessary.

Enter this table in A1:B5:

Then enter a separate ascending list in D1:D5: heading Threshold in D1, followed by 10, 20, 30, 40 in D2:D5. Use F2 onward for formulas.

=XMATCH("Pen",A2:A5)

Expected result: 1.

The first Pen occupies the first position inside A2:A5. This is not the worksheet row number: the cell is A2, but the returned relative position is 1.

=XMATCH("Box",A2:A5)

Expected result: 4. The header is not included in either lookup range.

=XMATCH("Pen",A2:A5,0,-1)

Expected result: 3.

The third argument keeps exact matching. The final -1 changes the search direction so the Pen in A4 is found first from the bottom. Its position remains 3 when counted from the beginning of A2:A5.

This is useful for a last recorded event only when the data order actually represents chronology. Sorting or appending older records can change the interpretation.

=INDEX(B2:B5,XMATCH("Pen",A2:A5,0,-1))

Expected result: 30.

XMATCH supplies position 3, and INDEX retrieves the third amount from B2:B5. Both ranges begin and end on the same rows. If the amount range accidentally starts at B3, a position may still look plausible while returning the wrong record.

Use XLOOKUP when you prefer a single function that directly returns the aligned value.

=XMATCH(25,D2:D5,-1)

Expected result: 2, the position of 20, the next smaller threshold.

=XMATCH(25,D2:D5,1)

Expected result: 3, the position of 30, the next greater threshold.

The two formulas answer different questions. For a minimum qualifying target, selecting the smaller threshold instead of the larger one can change a business decision without causing a formula error.

=XMATCH(30,D2:D5,0,2)

Expected result: 3. The range is deliberately ascending, as required by search mode 2. Use a normally ordered exact search when you cannot guarantee the sort order. Binary search is an algorithm choice, not a substitute for approximate matching, and it should not be used to establish first or last occurrence among duplicate keys.

For a simple wildcard example, =XMATCH("Pa*",A2:A5,2) returns 2, matching Pad. Wildcards belong to match mode 2; do not assume a regex pattern is a wildcard pattern.

• No exact match: inspect spaces, number-versus-text types and the range boundaries. Handle missing items separately with IFNA where appropriate.
• Wrong duplicate found: review search direction. Default lookup finds the first occurrence in this dataset.
• Wrong approximate answer: check the sign of match mode. -1 means next smaller; 1 means next greater.
• Unreliable binary-search answer: restore the required sort order or switch to a linear search mode.
• Range-shape error: supply a single row or column rather than the whole two-column table.

The four-argument signature resembles Excel XMATCH, but this guide only establishes the Google Sheets behavior documented above. Verify any additional Excel-specific matching options before converting a workbook.

• MATCH in Google Sheets
• INDEX in Google Sheets
• XLOOKUP in Google Sheets
• Editorial testing standards

Verification: All sample data is fictional. Positions and retrieved values were independently checked against the supplied lists; no application-generated result or screenshot is claimed.