Demystifying the Excel XMATCH Function
The XMATCH function is a powerful lookup function that can be used in Excel for more efficient data lookups. XMATCH can perform approximate and exact matches, as well as reverse searches. Additionally, XMATCH supports binary searches for even faster lookup speeds. In this blog post, we will discuss how to use the XMATCH function for power lookups in your Excel spreadsheets!
What Does The XMATCH Function Do?
XMATCH is a function that’s used to look up and return data from a specific cell or range of cells. It can be used to find values in vertical or horizontal ranges, can perform approximate and exact matches, and supports wildcards (* ?) for partial matches. In addition, XMATCH can search data starting from the first value or the last value (see match type and search mode details below).
Where To Find The XMATCH Function?
Option 1:
You can find the XMATCH function in the Formulas tab of Excel under Lookup & Reference
Option 2:
You can use Formula Builder to walk you through the formula step by step.
Option 3:
You can manually type the formula into any cell.
The Formula
=XMATCH (lookup_value, lookup_array, [match_mode], [search_mode])
- lookup_value (required) – The lookup value.
- lookup_array (required) – The array or range to search.
- match_mode (optional) – 0 = exact match (default), -1 = exact match or next smallest, 1 = exact match or next larger, 2 = wildcard match.
- search_mode (optional) – 1 = search from first (default), -1 = search from last, 2 = binary search ascending, -2 = binary search descending.
Let’s Take a Look at Some Real-World Examples
Excel Workbook
Coming Soon
Video Walkthrough
Coming Soon
Example 1 – Basic XMATCH
Coming Soon
Your turn: Use the workbook and XMATCH to follow the examples in blue and finish the practice formulas highlighted in green.
Example 2 – XMATCH Function using Match_Mode
Coming Soon
Your turn: Use the workbook and XMATCH to follow the examples in blue and finish the practice formulas highlighted in green.
Example 3 – XMATCH Function using Search_Mode
Coming Soon
Your turn: Use the workbook and XMATCH to follow the examples in blue and finish the practice formulas highlighted in green.
Benefits of XMATCH
XMATCH offers several important advantages, especially compared to MATCH:
- approximate, exact, and wildcard matches
- forward or reverse searches
- can still be used with the INDEX function
- binary searches for speed
- next largest or next smallest value
Office 365 Only
XMATCH is a new function and can only be found in Office 365. If you are using an older version of Excel it may be time to upgrade.
Let’s Recap
The XMATCH function is a powerful lookup function that can be used in Excel for more efficient data lookups. XMATCH can perform approximate and exact matches, as well as reverse searches. Additionally, XMATCH supports binary searches for even faster lookup speeds. In this blog post, we have discussed how to use the XMATCH function for power lookups in your Excel spreadsheets!
Have any questions? Are there other topics you would like us to cover? Leave a comment below and let us know! Make sure to subscribe to our Newsletter to receive exclusive financial news right to your inbox.
