fx
FunctionPedia
Quick Answer

XMATCH Function

Modern lookup that returns a position (MATCH replacement).

✓ Excel✓ Google SheetsExcel Microsoft 365

Syntax

XMATCH
(lookup_value, lookup_array, [match_mode], [search_mode])

Parameters

ParameterDescriptionRequired
lookup_valueThe value to find within lookup_array.Required
lookup_arrayThe range or array to search (a single row or column).Required
match_mode0 exact match (default), -1 exact or next smaller, 1 exact or next larger, 2 wildcard match.Optional
search_mode1 search from first (default), -1 search from last, 2 binary search ascending, -2 binary search descending.Optional

Basic Example

Find the position of "Apple" in a list

=XMATCH("Apple", A2:A10)
Result3

"Apple" is the third item in A2:A10, so XMATCH returns 3. The default is exact match.

Advanced Examples

Example 1: Wildcard match

Find the first item beginning with "App".

Use match_mode 2

=XMATCH("App*", A2:A10, 2)
Result: 1
With match_mode 2, "App*" matches the first item starting with "App".

Example 2: Approximate match (next smaller)

Find where 85 fits in an ascending sorted list.

Use match_mode 1 for exact-or-next-larger, or -1 for next smaller

=XMATCH(85, B2:B10, 1)
Result: 4
match_mode 1 returns the position of the largest value that is less than or equal to 85 (requires sorted data).

How XMATCH Works

XMATCH returns the relative position of lookup_value within lookup_array. match_mode controls matching: 0 (exact, default), -1 (exact or next smaller), 1 (exact or next larger), 2 (wildcard). search_mode controls direction: 1 (first, default), -1 (last), 2/-2 (binary search for sorted data). It is the modern replacement for MATCH and accepts arrays directly.

1
Define the lookup
Choose the value to find and the array to search.
2
Pick match and search modes
Add match_mode and search_mode as needed.
3
Use the result
Feed the position into INDEX or other functions.

Important Notes & Limitations

  • Microsoft 365 / Excel 2021+ only (also in newer Google Sheets).

  • Approximate modes (-1/1) require the array to be sorted correctly.

  • Wildcard mode requires match_mode 2.

Common Errors & Fixes

#N/AExact match (mode 0) found nothing, or spelling mismatch.

Fix: Use mode -1/1 for approximate matches, or verify the lookup value.

#VALUE! (bad search_mode)An invalid search_mode number was used.

Fix: Use 1, -1, 2, or -2.

Download Practice File

Practice XMATCH with Real Data

Download a sample CSV file with pre-populated data and practice exercises for the XMATCH function. Works in both Excel and Google Sheets.

Works in Google SheetsCompatible with ExcelIncludes exercises

File format: CSV (comma-separated values) - opens in Excel, Google Sheets, and all spreadsheet apps

Frequently Asked Questions

XMATCH vs MATCH?
XMATCH defaults to exact match, can search from the last, and supports wildcards and binary search; MATCH defaults to approximate.
What is the default match mode?
Exact match (0), unlike MATCH's default approximate mode.
Is XMATCH in Google Sheets?
Yes, in newer Google Sheets versions; older Sheets only have MATCH.