XMATCH function

• Supports enhanced match and search functionality
• Allows wildcard matches with a question mark (?) or asterisk (*)

Sample usage

Syntax

• search_key: The value to search for. For example, 42, "Cats", or B24.
• lookup_range: The range to consider for the search. This range must be a singular row or column.
• match_mode: [OPTIONAL: 0 by default] The manner in which to find a match for the search_key.
  • 0 is for an exact match.
  • 1 is for an exact match or the next value that's greater than the search_key.
  • -1 is for an exact match or the next value that's lesser than the search_key.
  • 2 is for a wildcard match.
• search_mode: [OPTIONAL: 1 by default] The manner in which to search through the lookup range.
  • 1 is to search from the first entry to the last.
  • -1 is to search from the last entry to the first.
  • 2 is to search through the range with binary search. The range needs to be sorted in ascending order first.
  • -2 is to search through the range with binary search. The range needs to be sorted in descending order first.

Examples

XMATCH on Sales rep column with match_mode and search_mode omitted. XMATCH for Total Amount sold with match_mode = 0 and search_mode = 1 and -1. XMATCH with horizontal matching. XMATCH with match_mode = 1 and match_mode = -1 and search_mode omitted.

Related functions

• XLOOKUP
• MATCH