The XMATCH function in Excel is a powerful and versatile tool that allows you to search for a specified item in a range or array and return the relative position of that item. It improves upon the older MATCH function by adding more options and flexibility, such as the ability to search from the end of an array or use wildcard characters.
Syntax
The syntax for the XMATCH function is as follows:
| Argument | Description |
|---|---|
| =XMATCH(lookup_value, lookup_array, [match_mode], [search_mode]) | Searches for the specified value and returns its position. |
lookup_value: The value you want to find.
lookup_array: The array or range of cells to search within.
match_mode: (Optional) The type of match: 0: Exact match (default). 1: Exact match or next largest. -1: Exact match or next smallest. 2: Wildcard match.
search_mode: (Optional) The direction to search: 1: Search from the beginning (default). -1: Search from the end. 2: Binary search in ascending order. -2: Binary search in descending order.
Example
Consider a scenario where you have a list of employees and their corresponding IDs. You want to find the position of a specific employee ID in the list. This example includes a table with employee names and their corresponding IDs, which is slightly more complex due to the need to match IDs.
| Employee Name | Employee ID |
|---|---|
| John Doe | E123 |
| Jane Smith | E456 |
| Emily Johnson | E789 |
| Michael Brown | E101 |
To find the position of Employee ID E789 in this list, you can use the XMATCH function:
Practice Exercise 1
Task: Use the XMATCH function to find the position of the product with the ID P104 in the following product list.
Practice Exercise 2
Task: Use the XMATCH function to find the position of the date 15/02/2024 in the following list of project milestones, search from the end of the list.
Solution for Exercise 1:
To find the position of the product with the ID P103 in the list, use the following formula:
=XMATCH("P103", B2:B5, 0)This formula searches for P103 in the Product ID column and returns its relative position in the range.
Solution for Exercise 2:
To find the position of the date 2024-02-15 by searching from the end of the list, use the following formula:
=XMATCH(DATE(2024,2,15), B2:B5, 0, -1)This formula searches for 2024-02-15 in the Date column, starting from the end of the list, and returns its relative position.