reverse lookup but returning multiple values

[lezawang]

Hi
Lets say I have a column like the one below. I want to find out which days had temperature 24 or above. or 24 and below.
So if the user enter

temp = 24

then excel will return these days

25/02/2018
04/07/2016
23/12/2016

Or excel will return all days which had 24 and below? is that possible? Thank you so much.



[TABLE="class: grid, width: 139"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]date[/TD]
[TD]temp[/TD]
[/TR]
[TR]
[TD="align: right"]11/12/2016[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]25/02/2018[/TD]
[TD="align: right"]29[/TD]
[/TR]
[TR]
[TD="align: right"]15/10/2017[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]14/07/2017[/TD]
[TD="align: right"]19[/TD]
[/TR]
[TR]
[TD="align: right"]18/01/2018[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]14/07/2017[/TD]
[TD="align: right"]19[/TD]
[/TR]
[TR]
[TD="align: right"]04/07/2016[/TD]
[TD="align: right"]26[/TD]
[/TR]
[TR]
[TD="align: right"]30/03/2016[/TD]
[TD="align: right"]16[/TD]
[/TR]
[TR]
[TD="align: right"]28/09/2018[/TD]
[TD="align: right"]22[/TD]
[/TR]
[TR]
[TD="align: right"]04/09/2018[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]23/12/2016[/TD]
[TD="align: right"]24[/TD]
[/TR]
[TR]
[TD="align: right"]21/02/2016[/TD]
[TD="align: right"]2[/TD]
[/TR]
</tbody>[/TABLE]

 

[offthelip]

You haven't specified what you mean by "excel will return all days which had 24 and below"
Have you considered just using conditional formatting to "return" all the days above or below the specfic temperature.

 

[Scott T]

Book1
	A	B	C	D
1	date	temp
2	12/11/2016	4
3	2/25/2018	29	iii
4	10/15/2017	2
5	7/14/2017	19
6	1/18/2018	1
7	7/14/2017	19
8	7/4/2016	26	iii
9	3/30/2016	16
10	9/28/2018	22
11	9/4/2018	6
12	12/23/2016	24	iii
13	2/21/2016	2
14
15
16	Temp	24
17
18		over		under
19		2/25/2018		12/11/2016
20		7/4/2016		10/15/2017
21		12/23/2016		7/14/2017
22				1/18/2018
23				7/14/2017
24				3/30/2016
25				9/28/2018
26				9/4/2018
27				12/23/2016
28				2/21/2016
29
30
31
32
Sheet1

Cell Formulas
Range	Formula
B19	{=IF(ROWS(B$19:B19)>COUNTIF($B$2:$B$13,">="&$B$16),"",INDEX($A$2:$A$13,SMALL(IF($B$2:$B$13>=$B$16,ROW($B$2:$B$13)-ROW($B$2)+1),ROWS(B$19:B19))))}
B20	{=IF(ROWS(B$19:B20)>COUNTIF($B$2:$B$13,">="&$B$16),"",INDEX($A$2:$A$13,SMALL(IF($B$2:$B$13>=$B$16,ROW($B$2:$B$13)-ROW($B$2)+1),ROWS(B$19:B20))))}
B21	{=IF(ROWS(B$19:B21)>COUNTIF($B$2:$B$13,">="&$B$16),"",INDEX($A$2:$A$13,SMALL(IF($B$2:$B$13>=$B$16,ROW($B$2:$B$13)-ROW($B$2)+1),ROWS(B$19:B21))))}
B22	{=IF(ROWS(B$19:B22)>COUNTIF($B$2:$B$13,">="&$B$16),"",INDEX($A$2:$A$13,SMALL(IF($B$2:$B$13>=$B$16,ROW($B$2:$B$13)-ROW($B$2)+1),ROWS(B$19:B22))))}
B23	{=IF(ROWS(B$19:B23)>COUNTIF($B$2:$B$13,">="&$B$16),"",INDEX($A$2:$A$13,SMALL(IF($B$2:$B$13>=$B$16,ROW($B$2:$B$13)-ROW($B$2)+1),ROWS(B$19:B23))))}
B24	{=IF(ROWS(B$19:B24)>COUNTIF($B$2:$B$13,">="&$B$16),"",INDEX($A$2:$A$13,SMALL(IF($B$2:$B$13>=$B$16,ROW($B$2:$B$13)-ROW($B$2)+1),ROWS(B$19:B24))))}
B25	{=IF(ROWS(B$19:B25)>COUNTIF($B$2:$B$13,">="&$B$16),"",INDEX($A$2:$A$13,SMALL(IF($B$2:$B$13>=$B$16,ROW($B$2:$B$13)-ROW($B$2)+1),ROWS(B$19:B25))))}
B26	{=IF(ROWS(B$19:B26)>COUNTIF($B$2:$B$13,">="&$B$16),"",INDEX($A$2:$A$13,SMALL(IF($B$2:$B$13>=$B$16,ROW($B$2:$B$13)-ROW($B$2)+1),ROWS(B$19:B26))))}
B27	{=IF(ROWS(B$19:B27)>COUNTIF($B$2:$B$13,">="&$B$16),"",INDEX($A$2:$A$13,SMALL(IF($B$2:$B$13>=$B$16,ROW($B$2:$B$13)-ROW($B$2)+1),ROWS(B$19:B27))))}
B28	{=IF(ROWS(B$19:B28)>COUNTIF($B$2:$B$13,">="&$B$16),"",INDEX($A$2:$A$13,SMALL(IF($B$2:$B$13>=$B$16,ROW($B$2:$B$13)-ROW($B$2)+1),ROWS(B$19:B28))))}
B29	{=IF(ROWS(B$19:B29)>COUNTIF($B$2:$B$13,">="&$B$16),"",INDEX($A$2:$A$13,SMALL(IF($B$2:$B$13>=$B$16,ROW($B$2:$B$13)-ROW($B$2)+1),ROWS(B$19:B29))))}
B30	{=IF(ROWS(B$19:B30)>COUNTIF($B$2:$B$13,">="&$B$16),"",INDEX($A$2:$A$13,SMALL(IF($B$2:$B$13>=$B$16,ROW($B$2:$B$13)-ROW($B$2)+1),ROWS(B$19:B30))))}
B31	{=IF(ROWS(B$19:B31)>COUNTIF($B$2:$B$13,">="&$B$16),"",INDEX($A$2:$A$13,SMALL(IF($B$2:$B$13>=$B$16,ROW($B$2:$B$13)-ROW($B$2)+1),ROWS(B$19:B31))))}
D19	{=IF(ROWS(B$19:B19)>COUNTIF($B$2:$B$13,"<="&$B$16),"",INDEX($A$2:$A$13,SMALL(IF($B$2:$B$13<=$B$16,ROW($B$2:$B$13)-ROW($B$2)+1),ROWS(B$19:B19))))}
D20	{=IF(ROWS(B$19:B20)>COUNTIF($B$2:$B$13,"<="&$B$16),"",INDEX($A$2:$A$13,SMALL(IF($B$2:$B$13<=$B$16,ROW($B$2:$B$13)-ROW($B$2)+1),ROWS(B$19:B20))))}
D21	{=IF(ROWS(B$19:B21)>COUNTIF($B$2:$B$13,"<="&$B$16),"",INDEX($A$2:$A$13,SMALL(IF($B$2:$B$13<=$B$16,ROW($B$2:$B$13)-ROW($B$2)+1),ROWS(B$19:B21))))}
D22	{=IF(ROWS(B$19:B22)>COUNTIF($B$2:$B$13,"<="&$B$16),"",INDEX($A$2:$A$13,SMALL(IF($B$2:$B$13<=$B$16,ROW($B$2:$B$13)-ROW($B$2)+1),ROWS(B$19:B22))))}
D23	{=IF(ROWS(B$19:B23)>COUNTIF($B$2:$B$13,"<="&$B$16),"",INDEX($A$2:$A$13,SMALL(IF($B$2:$B$13<=$B$16,ROW($B$2:$B$13)-ROW($B$2)+1),ROWS(B$19:B23))))}
D24	{=IF(ROWS(B$19:B24)>COUNTIF($B$2:$B$13,"<="&$B$16),"",INDEX($A$2:$A$13,SMALL(IF($B$2:$B$13<=$B$16,ROW($B$2:$B$13)-ROW($B$2)+1),ROWS(B$19:B24))))}
D25	{=IF(ROWS(B$19:B25)>COUNTIF($B$2:$B$13,"<="&$B$16),"",INDEX($A$2:$A$13,SMALL(IF($B$2:$B$13<=$B$16,ROW($B$2:$B$13)-ROW($B$2)+1),ROWS(B$19:B25))))}
D26	{=IF(ROWS(B$19:B26)>COUNTIF($B$2:$B$13,"<="&$B$16),"",INDEX($A$2:$A$13,SMALL(IF($B$2:$B$13<=$B$16,ROW($B$2:$B$13)-ROW($B$2)+1),ROWS(B$19:B26))))}
D27	{=IF(ROWS(B$19:B27)>COUNTIF($B$2:$B$13,"<="&$B$16),"",INDEX($A$2:$A$13,SMALL(IF($B$2:$B$13<=$B$16,ROW($B$2:$B$13)-ROW($B$2)+1),ROWS(B$19:B27))))}
D28	{=IF(ROWS(B$19:B28)>COUNTIF($B$2:$B$13,"<="&$B$16),"",INDEX($A$2:$A$13,SMALL(IF($B$2:$B$13<=$B$16,ROW($B$2:$B$13)-ROW($B$2)+1),ROWS(B$19:B28))))}
D29	{=IF(ROWS(B$19:B29)>COUNTIF($B$2:$B$13,"<="&$B$16),"",INDEX($A$2:$A$13,SMALL(IF($B$2:$B$13<=$B$16,ROW($B$2:$B$13)-ROW($B$2)+1),ROWS(B$19:B29))))}
D30	{=IF(ROWS(B$19:B30)>COUNTIF($B$2:$B$13,"<="&$B$16),"",INDEX($A$2:$A$13,SMALL(IF($B$2:$B$13<=$B$16,ROW($B$2:$B$13)-ROW($B$2)+1),ROWS(B$19:B30))))}
D31	{=IF(ROWS(B$19:B31)>COUNTIF($B$2:$B$13,"<="&$B$16),"",INDEX($A$2:$A$13,SMALL(IF($B$2:$B$13<=$B$16,ROW($B$2:$B$13)-ROW($B$2)+1),ROWS(B$19:B31))))}
Press CTRL+SHIFT+ENTER to enter array formulas.