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!

Read more Excel Tips & Tricks:

IBM Planning Analytics Tips & Tricks: Correctly Sum Rounded Numbers in Excel

IBM Planning Analytics Tips & Tricks: Excel Keyboard Navigation

IBM Planning Analytics Tips & Tricks: Excel OFFSET Function