Matching Column Data

[albert_de]

I am currently building a Weather Station Data spreadsheet that will include Annual Maximums and Minimums from the Raw Data Download.
The Raw Data grows monthly, row by row, over the years. Therefore I need to be able to search the entire Raw Data array but only search for a specific period within that array.

I have uploaded a sample spreadsheet to Dropbox:
Dropbox

My problem:

In this Spreadsheet I know how to find the Minimum Temperature from the Downloaded Raw Data for a specific month or year.
e.g. from actual spreadsheet: =MINIFS('Raw Data'!$C:$C,'Raw Data'!$C:$C,"<>0",'Raw Data'!$A:$A,"2023*")

What I can’t work out is how to find the correct Matching Date for that Minimum.
This is because I don't know how to specify a year to search within the array.

I have tried INDEX MATCH without correctly working out how to do this for this task. I don’t know how to write a formula that searches within the array for a specific part/year.

Maybe XLOOKUp is the way to go? But again, I don’t know how to write a formula that searches for the lookup_value within a specific year in only part of the array.
E.g: =XLOOKUP(D3,'Raw Data'!B1:B22,'Raw Data'!A1:A22)

Also, I am not sure if XLOOKUP can look to the left by more than 1 Column (the actual Raw Data tab contains up to 34 Columns to the right of the Date Column)?

Notes:

• The Date Column is TEXT format and not DATE Format. It will always be TEXT.
• 0=no data sampled in data download. It does not normally mean 0 degrees. 0’s therefore need to be ignored.
• It doesn’t matter if there are 2 or more identical values within a year.

Hoping that I have over thought this and that there is a simple answer! Thank you for your help.

 

[Sergius]

Is this what you wanted?

Weather Data.xlsx
	B	C	D
2		2022	2023
3		TRUE	TRUE
4	Annual Lowest Minimum	19,7	16,8
5	Date of Lowest Minimum	2022-12-31 02:00	2023-01-02 02:00
Summary

Cell Formulas
Range	Formula
C3	C3	=COUNTIF('Raw Data'!A9:A23,"2022*")>0
D3	D3	=COUNTIF('Raw Data'!A9:A23,"2023*")>0
C4	C4	=IF(C3=FALSE,"",MINIFS('Raw Data'!B2:B23,'Raw Data'!B2:B23,"<>0",'Raw Data'!A2:A23,"2022*"))
D4	D4	=IF(D3=FALSE,"",MINIFS('Raw Data'!B2:B23,'Raw Data'!B2:B23,"<>0",'Raw Data'!A2:A23,"2023*"))
C5	C5	=IF(C3=FALSE,"",INDEX(TEXT('Raw Data'!A2:A23,"YYYY-MM-DD HH:MM"),MATCH(C4,'Raw Data'!B2:B23,0)))
D5	D5	=IF(D3=FALSE,"",INDEX(TEXT('Raw Data'!A2:A23,"YYYY-MM-DD HH:MM"),MATCH(D4,'Raw Data'!B2:B23,0)))

 

[Kaper]

another option for C5:

=IF(C3=FALSE,"",INDEX('Raw Data'!A2:A23,MATCH(C4,IF(YEAR('Raw Data'!A2:A23)=C2,'Raw Data'!B2:B23,0),0)))

or adopted to copying formulas from column C to right:

Weather Data (1).xlsx
	A	B	C	D
1
2			2022	2023
3			TRUE	TRUE
4		Annual Lowest Minimum	19,7	16,8
5		Date of Lowest Minimum	2022-12-31 02:00	2023-01-02 02:00
Summary

Cell Formulas
Range	Formula
C3:D3	C3	=COUNTIF('Raw Data'!$A2:$A23,C2&"*")>0
C4:D4	C4	=IF(C3=FALSE,"",MINIFS('Raw Data'!$B2:$B23,'Raw Data'!$B2:$B23,"<>0",'Raw Data'!$A2:$A23,C2&"*"))
C5:D5	C5	=IF(C3=FALSE,"",INDEX('Raw Data'!$A2:$A23,MATCH(C4,IF(YEAR('Raw Data'!$A2:$A23)=C2,'Raw Data'!$B2:$B23,0),0)))

Final comments
- don't you have a chance for real 0 degrees as a minimum temperature?
- how about situation when there are several days with exactly the same minimum?

 

[albert_de]

Hi Sergius & Kaper.

Thank you both for your quick reply to my post. Both of your solutions work perfectly. Now I have to choose! Thank you very much for your expert guidance.

Kaper - It would be very rare if ever that we have a chance for real 0 degrees as a minimum temperature in Queensland, Australia. If we did, that would pose another problem for this spreadsheet!
I am not worried if there are several days with exactly the same minimum - as that is the minimum. The question is which date would be best to report, the earliest date or the latest date? For my purposes it probably doesn't matter.

 

[Kaper]

Glad we could help!

As for duplicating minimum dates (I introduced such example in raw data sheet):

If you could use Excel 365 just one formula (C3 and copied right) to do it all

Weather Data (1).xlsx
	A	B	C	D	E	F
1
2			2022	2023	2024
3		Annual Lowest Minimum	19,7	16,8	no data
4		Date(s) of Lowest Minimum	2022-12-31 02:00	2023-01-02 02:00
5			2022-12-31 11:00
6
Summary

Cell Formulas
Range	Formula
C3:C5,E3,D3:D4	C3	=LET(dta,'Raw Data'!$A$2:$C$24,ydta,FILTER(dta,(YEAR(TAKE(dta,,1))=C2)*(INDEX(dta,,2)<>0)),ymin,MIN(INDEX(ydta,,2)),IFERROR(VSTACK(ymin,FILTER(TAKE(ydta,,1),INDEX(ydta,,2)=ymin)),"no data"))
Dynamic array formulas.

But with Excel 2021 TAKE and VSTACK are (as far as I know) not available, so "two-formulas" version (C3 and C4 copied right) shall (? I don't have 2021 so cannot test) do:

Weather Data (1).xlsx
	A	B	C	D	E	F
1
2			2022	2023	2024
3		Annual Lowest Minimum	19,7	16,8	no data
4		Date(s) of Lowest Minimum	2022-12-31 02:00	2023-01-02 02:00	no data
5			2022-12-31 11:00
6
Summary (2)

Cell Formulas
Range	Formula
C3:E3	C3	=LET(dta,'Raw Data'!$A$2:$C$24,ydta,FILTER(dta,(YEAR(INDEX(dta,,1))=C2)*(INDEX(dta,,2)<>0)),IFERROR(MIN(INDEX(ydta,,2)),"no data"))
C4:C5,D4:E4	C4	=LET(dta,'Raw Data'!$A$2:$C$24,ydta,FILTER(dta,(YEAR(INDEX(dta,,1))=C2)*(INDEX(dta,,2)<>0)),ymin,MIN(INDEX(ydta,,2)),IFERROR(FILTER(INDEX(ydta,,1),INDEX(ydta,,2)=ymin),"no data"))
Dynamic array formulas.

 

[albert_de]

Thank you very much for that. I will have a good study of your formula.
Using the original formula suggestions, I am experiencing one incorrect match with both Temp and Date results. Spurious Temp and Year date are returned. It is very peculiar as the formula's are correct. And I can't replicate the issue anywhere else.

 

[Kaper]

As for possible source of problem:
I'd look whether all data in Raw Data sheet are the same format (it's about both A and B columns) - especially in row from which results are returned and these in row you expect to be returned.

 

[albert_de]

As best as I can tell the Raw Data has uniform formatting throughout.
I might rebuild the Raw Data etc. and see what happens!

 

[albert_de]

I have found what is happening with the non matching date.

The date INDEX MATCH formula is returning the first matching Temperature value from the entire column, that is from all Years. Not just the Year of the Temperature in question.

For example:

The formula being used to find the Lowest Temp for a specific year in the RAW Data is:

Records Tab, Cell H22:
=IF(Summary!H$6=FALSE,"",MINIFS('Raw Data'!$C:$C,'Raw Data'!$A:$A,"2025*"))

The formula being used to find the matching Date in the RAW Data is:

Records Tab, Cell H23:
=IF(H$22="","",INDEX(TEXT('Raw Data'!$A:$A,"YYYY-MM-DD HH:MM"),MATCH(H$22,'Raw Data'!$C:$C,0)))

There doesn't appear to be anything to Match the Year, there is only a Match to the Temperature (H$22). Can an additional Match criteria be added to the formula for the year?

This is what the RAW DATA table looks like:

Also on re making the Raw Data table I found that "-" is used when No Data is recorded, not "0". I must have manually changed the "-" in the past? I can't remember. But now "0" is a valid temperature in the spreadsheet!

 

[albert_de]

I'm sorry Kaper. You have already given me the answer in your post above:
=IF(C3=FALSE,"",INDEX('Raw Data'!A2:A23,MATCH(C4,IF(YEAR('Raw Data'!A2:A23)=C2,'Raw Data'!B2:B23,0),0)))

Or for the actual example in my last post:
=IF(Summary!H$6=FALSE,"",INDEX('Raw Data'!A:A,MATCH(H22,IF(YEAR('Raw Data'!A:A)=H21,'Raw Data'!C:C,0),0)))

This works perfectly

I am still to study your post on repeated Values. I am very keen to look at that.