|
Classic file types are no longer available for use as of January 2021. You can transition your classic files or download a PDF. Learn More

Workiva Support Center

XMATCH

XMATCH

  • Last updated on

Description

Use this function to search for a specified item in an array or range of cells and return the item's relative position.

XMATCH is an advanced version of the MATCH function. It can perform exact or approximate matches and can search in ascending or descending order.

XMATCH differs from MATCH as follows:

  • XMATCH supports binary search for faster performance on large, sorted datasets.
  • XMATCH allows for wild card characters in exact match mode.
  • XMATCH can return the next larger or smaller item if an exact match is not found.

 

Syntax

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

 

Inputs

This function accepts the following arguments:

Name Required Description Valid input
lookup_value

Yes

 The value to search for in the lookup_array. A number, a text string, a logical value, a cell reference, a cell range, or a formula which results in any of these. 
lookup_array

Yes

 The range of cells being searched. A cell reference, a cell range, or a formula which results in either of these. 
match_mode

No

 How to match lookup_value. 0 (exact match), [Default]
-1 (exact match or next smaller item)
1 (exact match or next larger item)
2 (wildcard match)
A formula which results in any of these is also acceptable.
search_mode

No

 The search mode to use. 1 (search first-to-last), [Default]
-1 (search last-to-first)
2 (binary search ascending)
-2 (binary search descending).
A formula which results in any of these is also acceptable.

 

Examples

Sample data

  A B  
1 Apple $1.25  
2 Banana $0.75  
3 Cherry $2.50  
4 Date $3.00  

Sample Formulas

Use case Formula Explanation and Result
Find the relative position of an exact value in a list. =XMATCH("Cherry", A2:A6)

This formula works in the following manner:

  • XMATCH(: This is the function itself.
  • "Cherry": The lookup_value.
  • A2:A6: The lookup_array.
  • The match_mode is omitted, defaulting to 0 (exact match).
  • The formula finds "Cherry" in the 3rd position of the array.

Result: 3.
Find the relative position of a value by finding the smallest value greater than or equal to the lookup value. =XMATCH(1, B2:B6, 1)

This formula works in the following manner:

  • XMATCH(: This is the function itself.
  • 1: The lookup_value.
  • B2:B6: The lookup_array.
  • 1: The match_mode, specifying an exact match or the next larger item.
  • The function finds the first item in the array that is greater than or equal to 1, which is 1.20 (in B5). This is the 4th item in the array B2:B6.

Result: 4.
Find a value in a list using a wildcard search. =XMATCH("G*", A2:A6, 2)

This formula works in the following manner:

  • XMATCH(: This is the function itself.
  • "G*": The lookup_value, using a wildcard.
  • A2:A6: The lookup_array.
  • 2: The match_mode, specifying a wildcard character match.
  • The formula searches for any text starting with "G". No items in the array match this pattern.

Result: #N/A.
Find the relative position of a value by searching from the last item in a list. =XMATCH("Date", A2:A6, 0, -1)

This formula works in the following manner:

  • XMATCH(: This is the function itself.
  • "Date": The lookup_value.
  • A2:A6: The lookup_array.
  • 0: The match_mode (exact match).
  • -1: The search_mode (search from last to first).
  • The formula finds "Date" at position 4 (A5).

Result: 4.
Find the relative position of a value by finding the largest value less than or equal to the lookup value. =XMATCH(1.5, B2:B6, -1)

This formula works in the following manner:

  • XMATCH(: This is the function itself.
  • 1.5: The lookup_value.
  • B2:B6: The lookup_array.
  • -1: The match_mode, specifying an exact match or the next *smaller* item.
  • The function finds the first item in the array that is less than or equal to 1.5. This is 1.20 (in B5), which is the 4th item in the array B2:B6.

Result: 4.

 

Notes

  • XMATCH returns the relative position of the matched item in the lookup_array.
  • If no match is found and an approximate match is not specified, XMATCH returns #N/A.
  • For binary search (search_mode = 2 or -2), the lookup_array must be sorted in ascending or descending order.
  • Wildcard characters (* and ?) are only supported when match_mode is set to 2.
  • XMATCH is case-insensitive when matching text values.

 

Tips

  • Use XMATCH with XLOOKUP for powerful, flexible lookup operations.
  • For large sorted datasets, use binary search (search_mode 2 or -2) for better performance.
  • When working with unsorted data, use search_mode 1 or -1 to ensure accurate results.
  • You can combine XMATCH with other functions such as INDEX for advanced data retrieval tasks.

 

Related functions

DescriptionUse this function to search for a specified item in an array or range of cells and return the item's relat...