XMATCH function

XMATCH returns the relative position of a lookup value within a one-dimensional range, supporting exact, wildcard and next-larger/smaller matches.

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

Generate a XMATCH formula

Describe what you need. The generator will reach for XMATCH where XMATCH is the right tool, and tell you when it is not.

How to get a better answer
  • Name your columns by letter and by header: "column F (Net Value)" beats "the amount column".
  • State every condition, including the negatives — "not cancelled" changes the formula's shape.
  • Say where the data starts if it is not row 1, and whether it will grow.
  • Check the settings above match your spreadsheet: the wrong argument separator is a syntax error on your machine.

Arguments

How XMATCH reads its arguments
lookup_valuerequiredlookup_arrayrequiredmatch_modeoptionalsearch_modeoptionalXMATCH →
ArgumentRequiredDescription
lookup_valueRequiredThe value to search for; can be text, number, or Boolean—if omitted the function returns #N/A.
lookup_arrayRequiredA single row or column range that XMATCH scans; multi-column ranges cause a #VALUE! error.
match_modeOptionalOptional integer 0 (exact, default), -1 (exact or next smaller), 1 (exact or next larger), or 2 (wildcard text); out-of-range values raise #NUM!.
search_modeOptionalOptional integer 1 (search first to last, default), -1 (search last to first), or 2 (binary search on sorted data); incorrect mode yields #NUM!.

Returns

A single numeric value indicating the position of the match within the lookup array.

Availability

Excel: 365 / 2021+ · Google Sheets: Supported

Worked examples

1. Locate first occurrence of a student name

Student
Alice
Bob
Alice
Bob
Alice
Bob
=XMATCH("Alice", $A$2:$A$7, 0, 1)

Result: 1

The formula looks for the exact text "Alice" in the Student column A2:A7. XMATCH scans from top to bottom (search_mode = 1) and finds "Alice" at the first position, so it returns 1. If "Alice" were not present, the function would return #N/A.

2. Find a score when searching from the bottom

Score
85
78
92
88
90
84
=XMATCH(88, $E$2:$E$7, 0, -1)

Result: 4

Here XMATCH seeks the exact numeric value 88 in the Score column E2:E7. The search_mode of -1 makes the search start at the bottom, but the returned position is still relative to the top of the range, so the match is at the fourth element. The function therefore returns 4.

3. Get position of the first score ≥ 80

Score
85
78
92
88
90
84
=XMATCH(80, $E$2:$E$7, 1, 1)

Result: 1

With match_mode = 1, XMATCH returns the position of the smallest value that is greater than or equal to the lookup_value. The first score that meets or exceeds 80 is 85, which resides at the first position in the range, so the result is 1. Changing match_mode to -1 would instead return the position of the next smaller value.

Common errors

Which XMATCH error are you seeing?
XMATCH returned an error#N/A
Use IFNA to handle the missing value or change match_mode to 1 or -1 to get the next larger or smaller entry.
#VALUE!
Restrict the reference to a single column or row, e.g., $A$2:$A$7 instead of $A$2:$C$7.
#NUM!
Convert the numeric value to text with TEXT() or use match_mode 0 for an exact numeric comparison.
ErrorWhy it happensHow to fix it
#N/AThe lookup_value does not exist in the lookup_array when match_mode is set to exact (0).Use IFNA to handle the missing value or change match_mode to 1 or -1 to get the next larger or smaller entry.
#VALUE!lookup_array contains more than one column or row, which XMATCH cannot process.Restrict the reference to a single column or row, e.g., $A$2:$A$7 instead of $A$2:$C$7.
#NUM!match_mode is 2 (wildcard) but the lookup_value is numeric, causing a type mismatch.Convert the numeric value to text with TEXT() or use match_mode 0 for an exact numeric comparison.

Tips and when to use something else

  • Use search_mode = 2 for a binary search on a sorted array; it runs faster than linear search.
  • When you need the actual related value (e.g., the subject for a student), pair XMATCH with INDEX instead of using XMATCH alone.
  • Wildcard matching (match_mode = 2) works only with text strings; wrap numbers in TEXT() if you need pattern matching.
  • If you want to return a value rather than a position, consider XLOOKUP, which combines lookup and return in one step.

Related functions

XLOOKUPXLOOKUP returns the value from a specified return array that corresponds to a given lookup value, with optional handling for missing matches and search direction.INDEXINDEX returns the value or array of values from a specified position within a given range, based on row and optional column numbers.MATCHMATCH returns the relative position of a lookup value within a one-dimensional range, optionally using exact, less-than, or greater-than matching.VLOOKUPVLOOKUP searches the first column of a table and returns a value from a specified column in the same row, optionally using exact or approximate matching.LOOKUPLOOKUP searches a one-dimensional range for a value and returns the corresponding entry from a result range, or the last value if no exact match is found.

Frequently asked questions

How does XMATCH differ from the older MATCH function?
XMATCH adds optional search_mode for reverse and binary searches, and it supports wildcard matching with match_mode = 2. It also returns the position of the next larger or smaller item, which MATCH cannot do without additional tricks.
Can XMATCH perform a binary search on unsorted data?
No. Binary search (search_mode = 2) requires the lookup_array to be sorted in ascending order for exact or next-larger matches, and descending order for next-smaller matches. Using it on unsorted data will produce incorrect results or a #NUM! error.
What does the search_mode argument control?
search_mode determines the direction and algorithm of the search: 1 scans from the first to the last element, -1 scans from the last to the first, and 2 performs a binary search on a sorted range, which is much faster for large datasets.
Why does XMATCH return #N/A for a value that I see in the list?
If the lookup_array contains hidden characters, leading/trailing spaces, or mismatched data types (e.g., number stored as text), XMATCH treats them as different values and returns #N/A. Clean the data with TRIM or VALUE, or use exact match_mode = 0 with appropriate type conversion.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator