Excel - Count how many months a specific metrics may be highest

Copper Contributor

I am attempting to create a comment generator for a variety of KPI's and am looking for excel to use a month selected via dropdown and subsequently read off of a Pivot Table (can use standard table references if needed) to determine if x value exceeded x amount of prior months performance in order of prior months from selected month. 

Ex: Sales in Apr was 50,000

Supplemental: Mar 43,000, Feb 22,000, Jan 48,000, Dec 56,000, Nov 60,000, Oct 49,000


I would like Excel to count until the value is exceeded while using EOM formula for standardization on date formats: Using Conconate the formula would fill the X months identified from the data set:

Ex: Best Sales month in 4 months

Ex: =Conconate("Best Sales month in ",(formula)," months"


Thank you for any insight/assistance.

1 Reply
I think you can do this using XMATCH instead. So if you have a row of data and use a formula like:
=XMATCH( [lookup Val], [row of Data], 1,1) -1
that should tell you how many previous months are smaller than this value