XMATCH function

The XMATCH function in Excel is used to find the relative position of a value in a range or array. It is similar to the MATCH function, but XMATCH offers enhanced functionality with more flexible search modes and match types.

Syntax

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

Parameters

• lookup_value: The value you want to search for in the lookup_array. This can be a number, text, logical value, or reference.
• lookup_array: The range or array in which you want to search for the lookup_value. It can be a row, column, or array.
• [match_mode] (optional): Specifies how to match the lookup_value:
  • 0 (default) – Exact match.
  • 1 – Exact match or next smaller item.
  • -1 – Exact match or next larger item.
• [search_mode] (optional): Specifies the direction of the search:
  • 1 (default) – Search from the first to the last (top to bottom, left to right).
  • -1 – Search from the last to the first (bottom to top, right to left).

How It Works

• XMATCH returns the position of the lookup_value within the lookup_array. The function returns an integer value representing the relative position of the matched item.
• It also supports approximate matching, so you can use it for range-based searches (similar to how MATCH is used).

Examples

Example 1: Exact Match

Let’s say you have a list of products in A2:A6, and you want to find the position of the product “Banana”:

You can use XMATCH like this:

=XMATCH("Banana", A2:A6)

This will return 2, because “Banana” is the second item in the range.

Example 2: Approximate Match (Next Smaller Item)

If you’re looking for an approximate match, you can use the [match_mode] parameter. For instance, let’s say you have a list of exam scores:

Now, to find the position of the score 60, which does not exist in the list, but you want to return the position of the next smaller item, use:

=XMATCH(60, A2:A6, 1)

This will return 3, as the closest smaller score is 50, which is in the third position.

Example 3: Reverse Search

If you want to search in reverse order (from bottom to top), you can use the [search_mode] parameter. For example, let’s say you have the following names:

To find the position of “Ben” starting from the bottom, you can use:

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

This will return 2, as “Ben” is in the second position when searching from the bottom up.

Example 4: Error Handling

If the value is not found in the array, XMATCH will return an error. You can handle this error using the IFERROR function:

=IFERROR(XMATCH("Grape", A2:A6), "Not Found")

This will return "Not Found", as “Grape” is not in the list.

Key Advantages of XMATCH over MATCH

1. Flexible Matching: You can control whether XMATCH finds an exact match or the next smaller or larger item.
2. Search Direction: You can search from top to bottom (default) or bottom to top.
3. Dynamic Arrays: Works seamlessly with dynamic arrays, meaning it can return multiple values when used with array formulas.
4. Better Performance: XMATCH is faster and more efficient compared to MATCH, especially with large datasets.

Use Case Scenarios

• Finding the Position of a Value: If you need to find where a value is located within a range, XMATCH is a straightforward and efficient option.
• Dynamic Array Formulas: XMATCH works well with Excel’s dynamic arrays, helping in scenarios where you need to reference or manipulate array data.

XMATCH is a versatile function that can be used in various contexts, whether you’re working with exact matches or performing approximate lookups. It’s especially useful when dealing with sorted data and dynamic arrays.