Extracting year with max value for calendar day Google Sheets

Question

I have a sheet with daily readings of lake level and temperature. I want to extract the max level for each day of the year, along with the year that the reading occurred. If it occurred more than once on the same day of the year, I'd like the first occurrence. It's easy enough to get the max for each day, but I haven't been able to also extract the year. I'm sure there are other people much more versed in GSheets than me who can figure this out. I also expect it's not that difficult, but I've yet to be able to find something that works.

Here's the sheet: https://docs.google.com/spreadsheets/d/1kC3L0oDpnnJlFFuB76mUXiz3vGcfT5_fC3YbkE9Xgsg

question from:https://stackoverflow.com/questions/65885177/extracting-year-with-max-value-for-calendar-day-google-sheets

Answer 1

UPDATE: The layout you needed was a little different than what I'd first thought. This QUERY will generate the maximum and minimums per day for your data. link to sample sheet

=ARRAYFORMULA(QUERY({1*(TEXT('raw data'!A2:A,"mmm d ")&2020),IF('raw data'!B2:C=0,,'raw data'!B2:C)},"select Col1,MAX(Col2),MIN(Col2),MAX(Col3),MIN(Col3) where Col1 is not null group by Col1 order by Col1 label Col1'Day'"))

Then this formula will use those days and values to do a VLOOKUP back into the Raw Data to show the first year on which that value occurred for that day.

=ARRAYFORMULA(VLOOKUP(N(C4:D)&TEXT(B4:B,"mmm d"),{'raw data'!B:B&TEXT('raw data'!A:A,"mmm d"),YEAR('raw data'!A:A)},2,0))

This spreadsheet will be available forever.