Alternatives for searching data from parameters

quemuenchatocha

New Member
Joined
Aug 4, 2021
Messages
17
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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
ABCDEFGHIJK
1
2
3
4
5
6MonthFebreroHigh Temp1
7Day26Low Temp2
8Year2017Precip3
9Snow4
105
116
127
138
14Record…Snow9
1510
1611
1712
18Year13
1914
2015
2116
2217
2318
2419
2520
2621
2722
2823
2924
3025
3126
3227
3328
34
wheater
Cell Formulas
RangeFormula
J6:J33J6=SEQUENCE(DATE(YEAR(C8),MONTH(C6&1)+1,1)-DATE(YEAR(C8),MONTH(C6&1),1))
Dynamic array formulas.
Cells with Data Validation
CellAllowCriteria
C14List=$E$6:$E$9
C6List=Data!$L$5:$L$16
C7List=$J$6#
C8List=Data!$K$5#


Example.xlsx
ABCDEFGHIJKLMN
1
2
3
4YearNumberMonthDayHighLowPrecipSnow
519991January14531-999-9991999JanuaryEnero
619991January23570,23,32000FebruaryFebrero
719991January32770,031,12001MarchMarzo
819991January44311002002AprilAbril
919991January54926002003MayMayo
1019991January65530002004JuneJunio
1119991January76023002005JulyJulio
1219991January841220,01-9992006AugustAgosto
1319991January94817002007SeptemberSeptiembre
1419991January105229002008OctoberOctubre
1519991January116240002009NovemberNoviembre
1619991January125734002010DecemberDiciembre
1719991January134223002011
1819991January145720002012
1919991January155942002013
2019991January165623002014
2119991January174729-999-9992015
2219991January185921-99902016
2319991January195735-99902017
2419991January205232002018
2519991January2146290,050,62019
2619991January2243200,222,92020
2719991January23542000
2819991January2450250,131,7
2919991January2537190,010,1
3019991January264822-999-999
3119991January274224-999-999
3219991January284217-999-999
3319991January29481900
3419991January30442200
3519991January31462300
3619992February146260-998
3719992February253260-998
3819992February354350-998
3919992February459220-998
4019992February553380-998
4119992February652250-998
4219992February768300-998
4319992February857350-998
4419992February960260-998
4519992February1066340,01-998
4619992February1134230,06-998
4719992February1253180-998
4819992February1370270-998
4919992February1468330-998
5019992February1555390-998
5119992February1650190-998
5219992February1751250-998
5319992February1847240-998
5419992February1951240-998
5519992February2047250-998
5619992February2145180-998
5719992February2244240,01-998
5819992February2357350-998
5919992February2462390-998
6019992February2566330-998
6119992February2664430-998
6219992February2755300-998
6319992February2864310-998
6419992February29-998-998-998-998
6519993March168420-998
6619993March254320-998
6719993March368210-998
6819993March461420-998
6919993March544280,73-998
7019993March639310,02-998
7119993March748170-998
7219993March851310-998
7319993March952220-998
7419993March1057330-998
7519993March1151250-998
7619993March1239270,34-998
7719993March1352270-998
7819993March1469290-998
7919993March1569450-998
8019993March1671430-998
8119993March1765300-998
8219993March1856240-998
8319993March1957290-998
8419993March2066330-998
8519993March2169320-998
8619993March2266300-998
8719993March2363300-998
8819993March2463320-998
8919993March2565320-998
9019993March2672320-998
9119993March2764480-998
9219993March2856330-998
9319993March2968260-998
9419993March3076370-998
9519993March3170390-998
Data
Cell Formulas
RangeFormula
K5:K26K5=UNIQUE(Year,FALSE)
C5:C95C5=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
NameRefers ToCells
Data=Data!$A$5:$H$8056K5
Number=Data!$B$5:$B$8056C5
Year=Data!$A$5:$A$8056K5


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!
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Crystalyzer

Well-known Member
Joined
Oct 18, 2011
Messages
688
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJ
1
2
3
4
5
6MonthJanuaryHigh Temp1
7Day3Low Temp2
8Year2017Precip3
9Snow4
105
116
127
138
14Record…Precip9
1510
160.0511
1712
18Year200013
1914
2015
2116
2217
2318
2419
2520
2621
2722
2823
2924
3025
3126
3227
3328
3429
3530
3631
Weather
Cell Formulas
RangeFormula
J6:J36J6=SEQUENCE(DATE(YEAR(C8),MONTH(C6&1)+1,1)-DATE(YEAR(C8),MONTH(C6&1),1))
C16C16=MAX((Month=Weather!C6)*(Day=Weather!C7)*INDIRECT(IF(ISERROR(FIND(" ",C14,1)),C14,MID(C14,1,FIND(" ",C14,1)-1))))
C18C18=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

New Member
Joined
Aug 4, 2021
Messages
17
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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.
 
Last edited by a moderator:

Crystalyzer

Well-known Member
Joined
Oct 18, 2011
Messages
688
Office Version
  1. 365
Platform
  1. Windows
Try entering it as an array formula CTL-SHFT-ENTER
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,748
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
BCDEFGHIJ
5
6MonthFebruaryHigh Temp641999, 20001
7Day26Low Temp4319992
8Year2000Precip1220003
9Snow-9981999, 20004
105
116
127
138
14Record…High Temp9
1510
166411
1712
18Year1999, 200013
1914
2015
2116
2217
2318
2419
2520
2621
2722
2823
2924
3025
3126
3227
3328
3429
35
wheater
Cell Formulas
RangeFormula
F6F6=MAX(FILTER(High,(mMonth=C6)*(dDay=C7)))
G6G6=TEXTJOIN(", ",1,FILTER(yYear,(mMonth=C6)*(dDay=C7)*(High=F6)))
F7F7=MIN(FILTER(Low,(mMonth=C6)*(dDay=C7)))
G7G7=TEXTJOIN(", ",1,FILTER(yYear,(mMonth=C6)*(dDay=C7)*(Low=F7)))
F8F8=MAX(FILTER(Precip,(mMonth=C6)*(dDay=C7)))
G8G8=TEXTJOIN(", ",1,FILTER(yYear,(mMonth=C6)*(dDay=C7)*(Precip=F8)))
F9F9=MAX(FILTER(Snow,(mMonth=C6)*(dDay=C7)))
G9G9=TEXTJOIN(", ",1,FILTER(yYear,(mMonth=C6)*(dDay=C7)*(Snow=F9)))
J6:J34J6=SEQUENCE(DAY(EDATE(DATEVALUE(C6&C8),1)-1))
C16C16=VLOOKUP(C14,E6:F9,2,0)
C18C18=VLOOKUP(C14,E6:G9,3,0)
Dynamic array formulas.
Named Ranges
NameRefers ToCells
Data=Data!$A$5:$H$8056G6:G9
dDay=Data!$D$5:$D$8056F6:G9
High=Data!$E$5:$E$8056F6:G6
Low=Data!$F$5:$F$8056F7:G7
mMonth=Data!$C$5:$C$8056F6:G9
Precip=Data!$G$5:$G$8056F8:G8
Snow=Data!$H$5:$H$8056F9:G9
yYear=Data!$A$5:$A$8056G6:G9
Cells with Data Validation
CellAllowCriteria
C14List=$E$6:$E$9
C6List=Data!$L$5:$L$16
C7ListJ6#
C8List=Data!K5#
 
Last edited:
Solution

quemuenchatocha

New Member
Joined
Aug 4, 2021
Messages
17
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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

New Member
Joined
Aug 4, 2021
Messages
17
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,748
Office Version
  1. 365
Platform
  1. Windows
You are very welcome. Thanks for the follow-up. :)
 

Forum statistics

Threads
1,147,750
Messages
5,742,981
Members
423,769
Latest member
LongToast

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top