Forum Discussion
Question related to year
Excel If the set-up is really as simple as you describe, and if you are willing to insert a helper column with a cumulative amount per year, XLOOKUP or MATCH could do this. See attached.
Thank you for giving the solution😊
Can you please explain this formula -
{=MATCH(TRUE,Cumulative>=12,0)}
Please?
From the inside out, "Cumulative>=F12" creates an array of <<FALSE, FASLE, TRUE, TRUE ....etc>> for every cell in the named range Cumulative. MATCH is finding the position of the first instance of TRUE in that array.
{=MATCH(TRUE,Cumulative>=F12,0)}So, in this particular example, it finds that the third element in the array is greater than or equal to 36000. Hence, during the third year, the indicated amount has been reached or exceeded.
The curly brackets around the formula indicate that you are an older version of Excel. You can't type them when you write the formula, but you need to finish the formula by pressing Ctrl-Shift-Enter simultaneously. Then, the curly brackets will appear. On newer Excel versions this is not necessary anymore.
