Formula for conditioned lookup

[Boki]

Hello All,

I'm having difficulties in writing functions for conditioned lookup. For
instance, I have columns:

Column A (Day): 1 2 3 4 5
Column B (Temp): 15 18 20 24 28

And two reference cells TL and TU

I want to lookup the temperature column (B) and return corresponding day(s)
in column (A) by inputing two numbers representing the lower (TL) and upper
(TU) limits of temperatures. That is to say, when I input TL=19 and TU=25,
the function will lookup all temperatures between 19-25 in Column B and
returns the corresponding days, i.e. 3 and 4 in this case, from Column A.

I could write lookup that finds the target with ONE reference value but not
a range of values.
Could anyone kindly advise me on how to creating this kind of lookup?

Thank you very much.
Bk

 

[radio848]

TEMPERATURE WORKSHEET


=F459






LAST YR
LO TEMP
PERIOD
HI TEMP
YEAR



RAIN/



=E3-1
AVG

=CHOOSE(J3,"January","February","March","April","May","June","July","August"
,"September","October","November","December")
AVG
=F459



SNOW
=MONTH(NOW())


AVG
------
------
------
------
------

------
------



=C463
=B451
=C452
=D451

Snow

Rain
=I451



------
------
------
------
------
-----
0.1
------
----



START

=DATE($E$3,1,1)









=MAX(A8:A38)
=AVERAGE(B8:B38)
=IF(C41>'2002'!C41,"Warmer","Colder")
=AVERAGE(D838)




=SUM(I8:I39)





=SUM(+B40+D40)/2






=INDEX($A8:$A38,MATCH(MIN($B$8:$B$38),$B$8:$B$38,0),1)
=MIN($B$8:$B$38)













START

=DATE($E$3,2,1)









=MAX(A44:A72)
=AVERAGE(B44:B71)
=IF(C75>'2002'!C75,"Warmer","Colder")
=AVERAGE(D4471)




=SUM(I44:I72)





=SUM(+B74+D74)/2






=INDEX($A42:$A72,MATCH(MIN($B$42:$B$72),$B$42:$B$72,0),1)
=MIN($B$44:$B$72)













START

=DATE($E$3,3,1)









=MAX(A78:A108)
=AVERAGE(B78:B108)
=IF(C111>'2002'!C111,"Warmer","Colder")
=AVERAGE(D78108)




=SUM(I78:I108)





=SUM(B110+D110)/2






=INDEX($A78:$A108,MATCH(MIN($B$78:$B$108),$B$78:$B$108,0),1)
=MIN($B$78:$B$108)













START

=DATE($E$3,4,1)









=MAX(A114:A143)
=AVERAGE(B114:B143)
=IF(C146>'2002'!C146,"Warmer","Colder")
=AVERAGE(D114143)




=SUM(I114:I143)





=SUM(+B145+D145)/2






=INDEX($A113:$A143,MATCH(MIN($B$113:$B$143),$B$113:$B$143,0),1)
=MIN($B$114:$B$143)













START

=DATE($E$3,5,1)









=MAX(A149:A179)
=AVERAGE(B149:B179)
=IF(C182>'2002'!C182,"Warmer","Colder")
=AVERAGE(D149179)




=SUM(I149:I179)





=SUM(B181+D181)/2






=INDEX($A149:$A179,MATCH(MIN($B$149:$B$179),$B$149:$B$179,0),1)
=MIN($B$149:$B$179)













START

=DATE($E$3,6,1)









=MAX(A185:A214)
=AVERAGE(B185:B214)
=IF(C217>'2002'!C217,"Warmer","Colder")
=AVERAGE(D185214)




=SUM(I185:I214)





=SUM(+B216+D216)/2






=INDEX($A184:$A214,MATCH(MIN($B$184:$B$214),$B$184:$B$214,0),1)
=MIN($B$185:$B$214)













START

=DATE($E$3,7,1)









=MAX(A220:A250)
=AVERAGE(B220:B250)
=IF(C253>'2002'!C253,"Warmer","Colder")
=AVERAGE(D220250)




=SUM(I220:I250)





=SUM(B252+D252)/2






=INDEX($A220:$A250,MATCH(MIN($B$220:$B$250),$B$220:$B$250,0),1)
=MIN($B$220:$B$250)













START

=DATE($E$3,8,1)









=A252+1
69.4
=CHOOSE(J256+1,$H$466,$H$467,$H$468,$H$469,$H$470,$H$471,$H$472)
101.8



4.3
=SUM(F256*$G$6)+H256
=MOD(A256,7)


=A256+1
61.2
=CHOOSE(J257+1,$H$466,$H$467,$H$468,$H$469,$H$470,$H$471,$H$472)
91.4



3.4
=SUM(F257*$G$6)+H257
=MOD(A257,7)


=A257+1
67.6
=CHOOSE(J258+1,$H$466,$H$467,$H$468,$H$469,$H$470,$H$471,$H$472)
88.7



0
=SUM(F258*$G$6)+H258
=MOD(A258,7)


=A258+1
70.3
=CHOOSE(J259+1,$H$466,$H$467,$H$468,$H$469,$H$470,$H$471,$H$472)
90.1



0.1
=SUM(F259*$G$6)+H259
=MOD(A259,7)


=A259+1
73.9
=CHOOSE(J260+1,$H$466,$H$467,$H$468,$H$469,$H$470,$H$471,$H$472)
95



0
=SUM(F260*$G$6)+H260
=MOD(A260,7)


=A260+1
70.2
=CHOOSE(J261+1,$H$466,$H$467,$H$468,$H$469,$H$470,$H$471,$H$472)
95



0
=SUM(F261*$G$6)+H261
=MOD(A261,7)


=A261+1
70.7
=CHOOSE(J262+1,$H$466,$H$467,$H$468,$H$469,$H$470,$H$471,$H$472)
91.2



0
=SUM(F262*$G$6)+H262
=MOD(A262,7)


=A262+1
68.5
=CHOOSE(J263+1,$H$466,$H$467,$H$468,$H$469,$H$470,$H$471,$H$472)
92.3



0
=SUM(F263*$G$6)+H263
=MOD(A263,7)


=A263+1
72.3
=CHOOSE(J264+1,$H$466,$H$467,$H$468,$H$469,$H$470,$H$471,$H$472)
92.8



0
=SUM(F264*$G$6)+H264
=MOD(A264,7)


=A264+1
67.5
=CHOOSE(J265+1,$H$466,$H$467,$H$468,$H$469,$H$470,$H$471,$H$472)
95.2



=0.9
=SUM(F265*$G$6)+H265
=MOD(A265,7)


=A265+1
66.6
=CHOOSE(J266+1,$H$466,$H$467,$H$468,$H$469,$H$470,$H$471,$H$472)
89.2



0
=SUM(F266*$G$6)+H266
=MOD(A266,7)


=A266+1
62.4
=CHOOSE(J267+1,$H$466,$H$467,$H$468,$H$469,$H$470,$H$471,$H$472)
90



0
=SUM(F267*$G$6)+H267
=MOD(A267,7)


=A267+1

=CHOOSE(J268+1,$H$466,$H$467,$H$468,$H$469,$H$470,$H$471,$H$472)


0
=SUM(F268*$G$6)+H268
=MOD(A268,7)


=A268+1

=CHOOSE(J269+1,$H$466,$H$467,$H$468,$H$469,$H$470,$H$471,$H$472)


0
=SUM(F269*$G$6)+H269
=MOD(A269,7)


=A269+1

=CHOOSE(J270+1,$H$466,$H$467,$H$468,$H$469,$H$470,$H$471,$H$472)


0
=SUM(F270*$G$6)+H270
=MOD(A270,7)


=A270+1

=CHOOSE(J271+1,$H$466,$H$467,$H$468,$H$469,$H$470,$H$471,$H$472)


0
=SUM(F271*$G$6)+H271
=MOD(A271,7)


=A271+1

=CHOOSE(J272+1,$H$466,$H$467,$H$468,$H$469,$H$470,$H$471,$H$472)


0
=SUM(F272*$G$6)+H272
=MOD(A272,7)


=A272+1

=CHOOSE(J273+1,$H$466,$H$467,$H$468,$H$469,$H$470,$H$471,$H$472)


0
=SUM(F273*$G$6)+H273
=MOD(A273,7)


=A273+1

=CHOOSE(J274+1,$H$466,$H$467,$H$468,$H$469,$H$470,$H$471,$H$472)


0
=SUM(F274*$G$6)+H274
=MOD(A274,7)


=A274+1

=CHOOSE(J275+1,$H$466,$H$467,$H$468,$H$469,$H$470,$H$471,$H$472)


0
=SUM(F275*$G$6)+H275
=MOD(A275,7)


=A275+1

=CHOOSE(J276+1,$H$466,$H$467,$H$468,$H$469,$H$470,$H$471,$H$472)


0
=SUM(F276*$G$6)+H276
=MOD(A276,7)


=A276+1

=CHOOSE(J277+1,$H$466,$H$467,$H$468,$H$469,$H$470,$H$471,$H$472)


0
=SUM(F277*$G$6)+H277
=MOD(A277,7)


=A277+1

=CHOOSE(J278+1,$H$466,$H$467,$H$468,$H$469,$H$470,$H$471,$H$472)


0
=SUM(F278*$G$6)+H278
=MOD(A278,7)


=A278+1

=CHOOSE(J279+1,$H$466,$H$467,$H$468,$H$469,$H$470,$H$471,$H$472)


0
=SUM(F279*$G$6)+H279
=MOD(A279,7)


=A279+1

=CHOOSE(J280+1,$H$466,$H$467,$H$468,$H$469,$H$470,$H$471,$H$472)


0
=SUM(F280*$G$6)+H280
=MOD(A280,7)


=A280+1

=CHOOSE(J281+1,$H$466,$H$467,$H$468,$H$469,$H$470,$H$471,$H$472)


0
=SUM(F281*$G$6)+H281
=MOD(A281,7)


=A281+1

=CHOOSE(J282+1,$H$466,$H$467,$H$468,$H$469,$H$470,$H$471,$H$472)


0
=SUM(F282*$G$6)+H282
=MOD(A282,7)


=A282+1

=CHOOSE(J283+1,$H$466,$H$467,$H$468,$H$469,$H$470,$H$471,$H$472)


0
=SUM(F283*$G$6)+H283
=MOD(A283,7)


=A283+1

=CHOOSE(J284+1,$H$466,$H$467,$H$468,$H$469,$H$470,$H$471,$H$472)


0
=SUM(F284*$G$6)+H284
=MOD(A284,7)


=A284+1

=CHOOSE(J285+1,$H$466,$H$467,$H$468,$H$469,$H$470,$H$471,$H$472)


0
=SUM(F285*$G$6)+H285
=MOD(A285,7)


=A285+1

=CHOOSE(J286+1,$H$466,$H$467,$H$468,$H$469,$H$470,$H$471,$H$472)


0
=SUM(F286*$G$6)+H286
=MOD(A286,7)





=COUNTIF(D256286,">100")
=INDEX($A256:$A286,MATCH(MAX(D256286),D256286,0),1)
=MAX(D256286)






=MAX(A256:A286)
=AVERAGE(B256:B286)
=IF(C289>'2002'!C289,"Warmer","Colder")
=AVERAGE(D256286)




=SUM(I256:I286)





=SUM(B288+D288)/2






=INDEX($A256:$A286,MATCH(MIN($B$256:$B$286),$B$256:$B$286,0),1)
=MIN($B$256:$B$286)













START

=DATE($E$3,9,1)









=MAX(A292:A321)
=AVERAGE(B292:B321)
=IF(C324>'2002'!C324,"Warmer","Colder")
=AVERAGE(D292321)




=SUM(I292:I321)





=SUM(B323+D323)/2






=INDEX($A291:$A321,MATCH(MIN($B$291:$B$321),$B$291:$B$321,0),1)
=MIN($B$292:$B$321)













START

=DATE($E$3,10,1)









=MAX(A327:A357)
=AVERAGE(B327:B357)
=IF(C360>'2002'!C360,"Warmer","Colder")
=AVERAGE(D327357)




=SUM(I327:I357)





=SUM(B359+D359)/2






=INDEX($A327:$A357,MATCH(MIN($B$327:$B$357),$B$327:$B$357,0),1)
=MIN($B$327:$B$357)













START

=DATE($E$3,11,1)









=MAX(A363:A392)
=AVERAGE(B363:B392)
=IF(C395>'2002'!C395,"Warmer","Colder")
=AVERAGE(D363392)




=SUM(I363:I392)





=SUM(B394+D394)/2






=INDEX($A362:$A392,MATCH(MIN($B$362:$B$392),$B$362:$B$392,0),1)
=MIN($B$363:$B$392)













START

=DATE($E$3,12,1)









=MAX(A398:A428)
=AVERAGE(B398:B428)
=IF(C431>'2002'!C431,"Warmer","Colder")
=AVERAGE(D398428)




=SUM(I398:I428)





=SUM(B430+D430)/2






=INDEX($A398:$A428,MATCH(MIN($B$398:$B$428),$B$398:$B$428,0),1)
=MIN($B$398:$B$428)














=INDEX($A$8:$A$428,MATCH(MIN(B8:B428),B8:B428,0),1)

=INDEX($A$8:$A$428,MATCH(MAX(D8428),D8428,0),1)








minimum
=MIN(B8:B428)

=MAX(D8428)
maximum







Less than freezing
=SUM(J39+J73+J109+J144+J180+J215+J251+J287+J322+J358+J393+J429)

=SUM(D39+D73+D109+D144+D180+D215+D251+D287+D322+D358+D393+D429)
Over 100














ELECT
RAIN



AVERAGE TEMP

=(E3)




DAILY
TOTAL



JAN
=IF(YEAR(NOW())<$C$437,'2002'!B438,B40)
Degrees
=IF(YEAR(NOW())<$C$437,'2002'!D438,D40)

=IF(YEAR(NOW())<$C$437,'2002'!F438,H438)
=
='C:\Documents and Settings\Owner\My
Documents\Budget\[ELECTRIC.xls]Elect-1-03'!$C$16
=IF(YEAR(NOW())<$C$437,'2002'!I438,I40)



FEB
=IF(YEAR(NOW())<$C$437,'2002'!B439,B74)
Degrees
=IF(YEAR(NOW())<$C$437,'2002'!D439,D74)

=IF(YEAR(NOW())<$C$437,'2002'!F439,H439)
=
='C:\Documents and Settings\Owner\My
Documents\Budget\[ELECTRIC.xls]Elect-2-03'!$C$16
=IF(YEAR(NOW())<$C$437,'2002'!I439,I74)



MAR
=IF(YEAR(NOW())<$C$437,'2002'!B440,B110)
Degrees
=IF(YEAR(NOW())<$C$437,'2002'!D440,D110)

=IF(YEAR(NOW())<$C$437,'2002'!F440,H440)
=
='C:\Documents and Settings\Owner\My
Documents\Budget\[ELECTRIC.xls]Elect-3-03'!$C$16
=IF(YEAR(NOW())<$C$437,'2002'!I440,I110)



APR
=IF(YEAR(NOW())<$C$437,'2002'!B441,B145)
Degrees
=IF(YEAR(NOW())<$C$437,'2002'!D441,D145)

=IF(YEAR(NOW())<$C$437,'2002'!F441,H441)
=
='C:\Documents and Settings\Owner\My
Documents\Budget\[ELECTRIC.xls]Elect-4-03'!$C$16
=IF(YEAR(NOW())<$C$437,'2002'!I441,I145)



MAY
=IF(YEAR(NOW())<$C$437,'2002'!B442,B181)
Degrees
=IF(YEAR(NOW())<$C$437,'2002'!D442,D181)

=IF(YEAR(NOW())<$C$437,'2002'!F442,H442)
=
='C:\Documents and Settings\Owner\My
Documents\Budget\[ELECTRIC.xls]Elect-5-03'!$C$16
=IF(YEAR(NOW())<$C$437,'2002'!I442,I181)



JUN
=IF(YEAR(NOW())<$C$437,'2002'!B443,B216)
Degrees
=IF(YEAR(NOW())<$C$437,'2002'!D443,D216)

=IF(YEAR(NOW())<$C$437,'2002'!F443,H443)
=
='C:\Documents and Settings\Owner\My
Documents\Budget\[ELECTRIC.xls]Elect-6-03'!$C$16
=IF(YEAR(NOW())<$C$437,'2002'!I443,I216)



JUL
=IF(YEAR(NOW())<$C$437,'2002'!B444,B252)
Degrees
=IF(YEAR(NOW())<$C$437,'2002'!D444,D252)

=IF(YEAR(NOW())<$C$437,'2002'!F444,H444)
=
='C:\Documents and Settings\Owner\My
Documents\Budget\[ELECTRIC.xls]Elect-7-03'!$C$16
=IF(YEAR(NOW())<$C$437,'2002'!I444,I252)



AUG
=IF(YEAR(NOW())<$C$437,'2002'!B445,B288)
Degrees
=IF(YEAR(NOW())<$C$437,'2002'!D445,D288)

=IF(YEAR(NOW())<$C$437,'2002'!F445,H445)
=
='C:\Documents and Settings\Owner\My
Documents\Budget\[ELECTRIC.xls]Elect-8-03'!$C$16
=IF(YEAR(NOW())<$C$437,'2002'!I445,I288)



SEP
=IF(YEAR(NOW())<$C$437,'2002'!B446,B323)
Degrees
=IF(YEAR(NOW())<$C$437,'2002'!D446,D323)

=IF(YEAR(NOW())<$C$437,'2002'!F446,H446)
=
='C:\Documents and Settings\Owner\My
Documents\Budget\[ELECTRIC.xls]Elect-9-03'!$C$16
=IF(YEAR(NOW())<$C$437,'2002'!I446,I323)



OCT
=IF(YEAR(NOW())<$C$437,'2002'!B447,B359)
Degrees
=IF(YEAR(NOW())<$C$437,'2002'!D447,D359)

=IF(YEAR(NOW())<$C$437,'2002'!F447,H447)
=
='C:\Documents and Settings\Owner\My
Documents\Budget\[ELECTRIC.xls]Elect-10-03'!$C$16
=IF(YEAR(NOW())<$C$437,'2002'!I447,I359)



NOV
=IF(YEAR(NOW())<$C$437,'2002'!B448,B394)
Degrees
=IF(YEAR(NOW())<$C$437,'2002'!D448,D394)

=IF(YEAR(NOW())<$C$437,'2002'!F448,H448)
=
='C:\Documents and Settings\Owner\My
Documents\Budget\[ELECTRIC.xls]Elect-11-03'!$C$16
=IF(YEAR(NOW())<$C$437,'2002'!I448,I394)



DEC
=IF(YEAR(NOW())<$C$437,'2002'!B449,B430)
Degrees
=IF(YEAR(NOW())<$C$437,'2002'!D449,D430)

=IF(YEAR(NOW())<$C$437,'2002'!F449,H449)
=
='C:\Documents and Settings\Owner\My
Documents\Budget\[ELECTRIC.xls]Elect-12-03'!$C$16
=IF(YEAR(NOW())<$C$437,'2002'!I449,I430)

=IF((YEAR(NOW())-1900)=$C$437," ",IF(K20=0," ",K51))














=AVERAGE(B438:B449)
=IF(C452<D463,$A$462,$A$461)
=AVERAGE(D438449)




=SUM(I438:I449)





=SUM(B451+D451)/2




=AVERAGE(H438:H449)
=AVERAGE(I438:I449)




Avg Low

Avg High









=A3

=A3







='2002'!B451
<<<<>>>>>
='2002'!D451
<<<These Cells Are Linked to B451








=SUM(B455+D455)/2

& D451 Of Last Years' Spread Sheet.































Note:
=A3

='2002'!F459+1
<<Enter Current Year






Enter this cell = to









Warmer

last year's avg temp









Colder

cell C452











=C456
=C463




















=C452
Farenheit
=SUM($A$465-32)*5/9
Celcius














0
Saturday










1
Sunday










2
Monday










3
Tuesday










4
Wednesday










5
Thursday










6
Friday






Hi Boki:
This is probably the wrong way to do this, but here goes anyway. I believe
that all of what you're seeking is here. Conditional formating can be used
to high-lite cell that fall in a particular range of temps.

 

[radio848]

Whoops:
That didn't work the way I wanted it to. There was supposed to be a
worksheet with the formulae in the appropriate places. I will rethink this
and try again. Sorry!!

 

[BK]

Hi Rob,

Thanks for your message.
I have to do it in excel because the job includes also many other tasks of
data analysis which needs to work in excel. So what you mean is that there
are ways to do it in excel except writing a VB?

BK

 

[Debra Dalgleish]

You could use an Advanced Filter to extract the days. There are
instructions here for Advanced Filters:
http://www.contextures.com/xladvfilter01.html

Your criteria range would have two columns with the same heading as
column B, and criteria in the next row, e.g.:
Temp Temp

 

[Boki]

Hi, Thanks a lot.

instructions here for Advanced Filters:
http://www.contextures.com/xladvfilter01.html

Your criteria range would have two columns with the same heading as
column B, and criteria in the next row, e.g.:
Temp Temp