CODEWITHSUNDEEP
sortinggoogle-sheetssplitgoogle-sheets-formulaflatten
Richard
Richard

Reputation: 37

Formula for searching a range and returning column headers

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.

Link to Google Sheet

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.

Upvotes: 1

Views: 214

Answers (1)

player0
player0

Reputation: 1

try:

=FLATTEN(SPLIT(INDEX(SORT(SPLIT(
 FLATTEN(B13:M13&"♦"&A14:A21&"♠"&B14:M21), "♠"), 2, 0), 1, 1), "♦"))

enter image description here

Upvotes: 1

Related Questions