IBM Planning Analytics Tips & Tricks: Excel’s XMATCH Function

May 17, 2022

Have you ever wanted to search a list and define the position where it appears in the list? Maybe you want to know how many sales reps exceeded their quota or want to define how many products cost more than an approved purchase price. This information can be calculated using Excel’s XMATCH function.

The XMATCH function is used to determine the number position of an item in a list.

The syntax of the function is:

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

Lookup_value
- This is required
Lookup_array
- This is required
Match_mode
- This is optional
- This determines whether you are looking for an exact match or whether you want to look for the next smallest or largest item
Search_mode
- This is optional
- This defines various approaches for top-to-bottom or bottom-to-top searches

The following example shows how to determine the number of sales reps that exceeded a sales quota. The lookup value is defined as the quota amount, the lookup array defines a list of sales amounts, and the match mode defines “Exact match or next largest item.”

This formula can be used independently to find vales within a list and can also be combined with other formulas such as COUNT to define the “not exceeded” values.

IBM Planning Analytics, which TM1 is the engine for, is full of new features and functionality. Not sure where to start? Our team here at Revelwood can help. Contact us for more information at info@revelwood.com. And stay tuned for more Planning Analytics Tips & Tricks weekly in our Knowledge Center and in upcoming newsletters!

Author

Lee Lazarow

View all posts