I’ve been working on, or at least trying to develop, a formula that goes through an array of cells, finds the max value and returns a cell that has the month that max value was found in.
The array I’m trying to search through is B14:M21. Once the max value is found in a column, I’d like to return a value from B13:M13. This return value (the month column where the value was found) is going to be displayed in cell H4.
This same concept would also need to be applied to finding the year the max value is found, just in terms of rows instead of columns. I’ve tried doing hlookup, vlookup, query, index/match, etc. and none of it has worked for this application.
>Solution :
try:
=FLATTEN(SPLIT(INDEX(SORT(SPLIT(
FLATTEN(B13:M13&"♦"&A14:A21&"♠"&B14:M21), "♠"), 2, 0), 1, 1), "♦"))
