Alternatives for searching data from parameters

[quemuenchatocha]

Dear all, have a very nice day

I apologize for the lengthy statement, but I want the problem to be well formulated to avoid confusion.

In the 'Data' worksheet, you will find information that compiles the weather conditions recorded daily for any given city from January 1, 1999 to December 31, 2020 concerning high temperatures, low temperatures, precipitation and snowfall.

Example.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1
2
3
4
5
6		Month	Febrero		High Temp					1
7		Day	26		Low Temp					2
8		Year	2017		Precip					3
9					Snow					4
10										5
11										6
12										7
13										8
14		Record…	Snow							9
15										10
16										11
17										12
18		Year								13
19										14
20										15
21										16
22										17
23										18
24										19
25										20
26										21
27										22
28										23
29										24
30										25
31										26
32										27
33										28
34
wheater

Cell Formulas
Range	Formula
J6:J33	J6	=SEQUENCE(DATE(YEAR(C8),MONTH(C6&1)+1,1)-DATE(YEAR(C8),MONTH(C6&1),1))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
C14	List	=$E$6:$E$9
C6	List	=Data!$L$5:$L$16
C7	List	=$J$6#
C8	List	=Data!$K$5#

Example.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2
3
4	Year	Number	Month	Day	High	Low	Precip	Snow
5	1999	1	January	1	45	31	-999	-999			1999	January	Enero
6	1999	1	January	2	35	7	0,2	3,3			2000	February	Febrero
7	1999	1	January	3	27	7	0,03	1,1			2001	March	Marzo
8	1999	1	January	4	43	11	0	0			2002	April	Abril
9	1999	1	January	5	49	26	0	0			2003	May	Mayo
10	1999	1	January	6	55	30	0	0			2004	June	Junio
11	1999	1	January	7	60	23	0	0			2005	July	Julio
12	1999	1	January	8	41	22	0,01	-999			2006	August	Agosto
13	1999	1	January	9	48	17	0	0			2007	September	Septiembre
14	1999	1	January	10	52	29	0	0			2008	October	Octubre
15	1999	1	January	11	62	40	0	0			2009	November	Noviembre
16	1999	1	January	12	57	34	0	0			2010	December	Diciembre
17	1999	1	January	13	42	23	0	0			2011
18	1999	1	January	14	57	20	0	0			2012
19	1999	1	January	15	59	42	0	0			2013
20	1999	1	January	16	56	23	0	0			2014
21	1999	1	January	17	47	29	-999	-999			2015
22	1999	1	January	18	59	21	-999	0			2016
23	1999	1	January	19	57	35	-999	0			2017
24	1999	1	January	20	52	32	0	0			2018
25	1999	1	January	21	46	29	0,05	0,6			2019
26	1999	1	January	22	43	20	0,22	2,9			2020
27	1999	1	January	23	54	20	0	0
28	1999	1	January	24	50	25	0,13	1,7
29	1999	1	January	25	37	19	0,01	0,1
30	1999	1	January	26	48	22	-999	-999
31	1999	1	January	27	42	24	-999	-999
32	1999	1	January	28	42	17	-999	-999
33	1999	1	January	29	48	19	0	0
34	1999	1	January	30	44	22	0	0
35	1999	1	January	31	46	23	0	0
36	1999	2	February	1	46	26	0	-998
37	1999	2	February	2	53	26	0	-998
38	1999	2	February	3	54	35	0	-998
39	1999	2	February	4	59	22	0	-998
40	1999	2	February	5	53	38	0	-998
41	1999	2	February	6	52	25	0	-998
42	1999	2	February	7	68	30	0	-998
43	1999	2	February	8	57	35	0	-998
44	1999	2	February	9	60	26	0	-998
45	1999	2	February	10	66	34	0,01	-998
46	1999	2	February	11	34	23	0,06	-998
47	1999	2	February	12	53	18	0	-998
48	1999	2	February	13	70	27	0	-998
49	1999	2	February	14	68	33	0	-998
50	1999	2	February	15	55	39	0	-998
51	1999	2	February	16	50	19	0	-998
52	1999	2	February	17	51	25	0	-998
53	1999	2	February	18	47	24	0	-998
54	1999	2	February	19	51	24	0	-998
55	1999	2	February	20	47	25	0	-998
56	1999	2	February	21	45	18	0	-998
57	1999	2	February	22	44	24	0,01	-998
58	1999	2	February	23	57	35	0	-998
59	1999	2	February	24	62	39	0	-998
60	1999	2	February	25	66	33	0	-998
61	1999	2	February	26	64	43	0	-998
62	1999	2	February	27	55	30	0	-998
63	1999	2	February	28	64	31	0	-998
64	1999	2	February	29	-998	-998	-998	-998
65	1999	3	March	1	68	42	0	-998
66	1999	3	March	2	54	32	0	-998
67	1999	3	March	3	68	21	0	-998
68	1999	3	March	4	61	42	0	-998
69	1999	3	March	5	44	28	0,73	-998
70	1999	3	March	6	39	31	0,02	-998
71	1999	3	March	7	48	17	0	-998
72	1999	3	March	8	51	31	0	-998
73	1999	3	March	9	52	22	0	-998
74	1999	3	March	10	57	33	0	-998
75	1999	3	March	11	51	25	0	-998
76	1999	3	March	12	39	27	0,34	-998
77	1999	3	March	13	52	27	0	-998
78	1999	3	March	14	69	29	0	-998
79	1999	3	March	15	69	45	0	-998
80	1999	3	March	16	71	43	0	-998
81	1999	3	March	17	65	30	0	-998
82	1999	3	March	18	56	24	0	-998
83	1999	3	March	19	57	29	0	-998
84	1999	3	March	20	66	33	0	-998
85	1999	3	March	21	69	32	0	-998
86	1999	3	March	22	66	30	0	-998
87	1999	3	March	23	63	30	0	-998
88	1999	3	March	24	63	32	0	-998
89	1999	3	March	25	65	32	0	-998
90	1999	3	March	26	72	32	0	-998
91	1999	3	March	27	64	48	0	-998
92	1999	3	March	28	56	33	0	-998
93	1999	3	March	29	68	26	0	-998
94	1999	3	March	30	76	37	0	-998
95	1999	3	March	31	70	39	0	-998
Data

Cell Formulas
Range	Formula
K5:K26	K5	=UNIQUE(Year,FALSE)
C5:C95	C5	=IF(B5=1,$L$5,IF(B5=2,$L$6,IF(B5=3,$L$7,IF(B5=4,$L$8,IF(B5=5,$L$9,IF(B5=6,$L$10,IF(B5=7,$L$11,IF(B5=8,$L$12,IF(B5=9,$L$13,IF(B5=10,$L$14,IF(B5=11,$L$15,$L$16)))))))))))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
Data	=Data!$A$5:$H$8056	K5
Number	=Data!$B$5:$B$8056	C5
Year	=Data!$A$5:$A$8056	K5

Additionally I have assigned range names to

Name Refers To Cells
Year =Data!$A$5:$A$8056 A4
Month =Data!$C$5:$C$8056 C4
Day =Data!$D$5:$D$8056 D4
High =Data!$E$5:$E$8056 E4
Low =Data!$F$5:$F$8056 F4
Precip =Data!$G$5:$G$8056 G4
Snow =Data!$H$5:$H$8056 H4

I am looking for the optimal combination of functions that will allow me to perform the following searches

i. In the 'wheater' worksheet, taking into account the month (C6) and the day (C7), independently of the year, I am trying to return in cell C16 the data corresponding to the highest temperature (High), the lowest temperature (Low); the maximum precipitation (Precip) or the maximum snow (Snow), as the case may be, recorded for the dates supplied in cells C6 and C7.

In other words, I want to find the maximum(minimum) temperature of, for example, every 26th of February of all year and record it in cell C16.

ii. The highest temperature (High), the lowest temperature (Low); the maximum precipitation (Precip) and the maximum snow (Snow), have been recorded in a validation list in cell C14, so when selecting each of the options, it should yield the corresponding value in cell C16. The problem I have encountered so far is that these names do not correspond to the table headers in the 'Data' worksheet (E4:H4), so bringing in the data is a bit complex for me.

iii. Finally, depending on the option selected in cell C14, I want to know in which year the temperature variation recorded in cell C16 was recorded, according to the dates provided in cells C6 and C7, so this value should appear in cell C18.

I have tried several combinations with INDEX, MATCH, OFFSET, MINIFS, MAXIFS but none of them have worked so far.

One option may be that in the validation list (C14), the names are exactly the same as the table headers registered in the worksheet 'Data' (E4:H4).

I don't know if it would be more convenient to convert the data from the 'Data' worksheet into a table.

I am using Excel 365

I appreciate your kind attention and the patience to read the whole approach, thank you very much!

 

[Crystalyzer]

I think this solves it for you. NOTE: I added some data with the year 2000 and changed a couple of values in the data

Book4
	A	B	C	D	E	F	G	H	I	J
1
2
3
4
5
6		Month	January		High Temp					1
7		Day	3		Low Temp					2
8		Year	2017		Precip					3
9					Snow					4
10										5
11										6
12										7
13										8
14		Record…	Precip							9
15										10
16			0.05							11
17										12
18		Year	2000							13
19										14
20										15
21										16
22										17
23										18
24										19
25										20
26										21
27										22
28										23
29										24
30										25
31										26
32										27
33										28
34										29
35										30
36										31
Weather

Cell Formulas
Range	Formula
J6:J36	J6	=SEQUENCE(DATE(YEAR(C8),MONTH(C6&1)+1,1)-DATE(YEAR(C8),MONTH(C6&1),1))
C16	C16	=MAX((Month=Weather!C6)*(Day=Weather!C7)*INDIRECT(IF(ISERROR(FIND(" ",C14,1)),C14,MID(C14,1,FIND(" ",C14,1)-1))))
C18	C18	=MAX(Year*(INDIRECT(IF(ISERROR(FIND(" ",C14,1)),C14,MID(C14,1,FIND(" ",C14,1)-1)))=MAX((Month=Weather!C6)*(Day=Weather!C7)*INDIRECT(IF(ISERROR(FIND(" ",C14,1)),C14,MID(C14,1,FIND(" ",C14,1)-1))))))
Dynamic array formulas.

 

[quemuenchatocha]

I think this solves it for you. NOTE: I added some data with the year 2000 and changed a couple of values in the data

Dear @Crystalyzer, I wanted to thank you for your attention and collaboration in solving my doubts. With respect to the formula you propose for cell C16 I get an error, in the IF(ISERROR(FIND(" ",C14,1),***) part it asks me for a value for the value argument if it is error, it is not entirely clear to me what may be missing in the solution, if you could guide me please I would appreciate it. Thanks again for your guidance.

 

[Crystalyzer]

Try entering it as an array formula CTL-SHFT-ENTER

 

[Peter_SSs]

Firstly, I would say that using named range names like 'Year' and 'Day', where Excel has native functions with the same names, is a bad idea. Hence, in my suggestion below I have slightly altered those names.

I am looking for the optimal combination of functions that will allow me to perform the following searches

I would approach it like this to keep the formulas pretty simple and non-volatile.

I have used helper cells in F6:G9 but these could be anywhere and the columns could be hidden if you want. Or you might even choose to show all these results instead of choosing the drop-down value in C14.

You will see that I have also catered for the situation where the 'record' value has occurred in multiple years. If there could be many years with equal records then that formula could be adapted to say "More than three" if that was the case or it could just list the most recent year or longest ago year.

Perhaps you might also want to do something different with snow values like that shown?

One further point is that your 'wheater'J6 formula looks a bit odd to me (and does not produce 29 for a leap year Feb though that may be unimportant) so I have suggested a simpler alternative.

quemuenchatocha_1.xlsm
	B	C	D	E	F	G	H	I	J
5
6	Month	February		High Temp	64	1999, 2000			1
7	Day	26		Low Temp	43	1999			2
8	Year	2000		Precip	12	2000			3
9				Snow	-998	1999, 2000			4
10									5
11									6
12									7
13									8
14	Record…	High Temp							9
15									10
16		64							11
17									12
18	Year	1999, 2000							13
19									14
20									15
21									16
22									17
23									18
24									19
25									20
26									21
27									22
28									23
29									24
30									25
31									26
32									27
33									28
34									29
35
wheater

Cell Formulas
Range	Formula
F6	F6	=MAX(FILTER(High,(mMonth=C6)*(dDay=C7)))
G6	G6	=TEXTJOIN(", ",1,FILTER(yYear,(mMonth=C6)*(dDay=C7)*(High=F6)))
F7	F7	=MIN(FILTER(Low,(mMonth=C6)*(dDay=C7)))
G7	G7	=TEXTJOIN(", ",1,FILTER(yYear,(mMonth=C6)*(dDay=C7)*(Low=F7)))
F8	F8	=MAX(FILTER(Precip,(mMonth=C6)*(dDay=C7)))
G8	G8	=TEXTJOIN(", ",1,FILTER(yYear,(mMonth=C6)*(dDay=C7)*(Precip=F8)))
F9	F9	=MAX(FILTER(Snow,(mMonth=C6)*(dDay=C7)))
G9	G9	=TEXTJOIN(", ",1,FILTER(yYear,(mMonth=C6)*(dDay=C7)*(Snow=F9)))
J6:J34	J6	=SEQUENCE(DAY(EDATE(DATEVALUE(C6&C8),1)-1))
C16	C16	=VLOOKUP(C14,E6:F9,2,0)
C18	C18	=VLOOKUP(C14,E6:G9,3,0)
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
Data	=Data!$A$5:$H$8056	G6:G9
dDay	=Data!$D$5:$D$8056	F6:G9
High	=Data!$E$5:$E$8056	F6:G6
Low	=Data!$F$5:$F$8056	F7:G7
mMonth	=Data!$C$5:$C$8056	F6:G9
Precip	=Data!$G$5:$G$8056	F8:G8
Snow	=Data!$H$5:$H$8056	F9:G9
yYear	=Data!$A$5:$A$8056	G6:G9

Cells with Data Validation
Cell	Allow	Criteria
C14	List	=$E$6:$E$9
C6	List	=Data!$L$5:$L$16
C7	List	J6#
C8	List	=Data!K5#

 

[quemuenchatocha]

Try entering it as an array formula CTL-SHFT-ENTER

Dear @Crystalyzer, I have tried once again, following your recommendations, but nevertheless I get again the message "You've entered too few arguments for this function", pointing out to me that the argument for "value_if_error" is missing, in the IF(ISERROR(FIND(" ",C14,1),XXXX) part of the function.

 

[quemuenchatocha]

Firstly, I would say that using named range names like 'Year' and 'Day', where Excel has native functions with the same names, is a bad idea. Hence, in my suggestion below I have slightly altered those names.

Dear @Peter_SSs as always your help is very valuable and constructive. Thank you for your kind advice, it is useful and solves my concerns that arose in the development of the activity. Personally I did not know the TEXTJOIN and DATEVAUE functions, they are new to me, I thank you for teaching them to me in this very practical way. Once again I thank you for your time and dedication in solving my doubts, a big hug!

 

[Peter_SSs]

You are very welcome. Thanks for the follow-up.