multiple lookup question

[lezawang]

Hi
I would like to find out those days that either NY temperature of 29 or higher or LA had a temperature 26 or higher. I would like to know the date that it occurred and what the Price and Nextday-prices were. Any help would be very much appreciated. Thank you.

Sample of the data

Date	NY temperature	LA temperature	Price	Nextday price
02/07/2018	32.22	26.91	49.92	69.88
03/07/2018	32.85	28.51	48.81	54.3
05/07/2018	30.69	24.58	53.43	43.77
06/07/2018	28.92	23.16	30.02	35.19
09/07/2018	27.74	25.52	40.59	34.6
10/07/2018	32.22	26.98	44.87	42.53
11/07/2018	31.08	24.13	42.01	38.76
12/07/2018	28.51	23.96	34.69	36.94

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

.
.
.

24/09/2018	19.06	17.15	23.27	31.95
25/09/2018	24.55	20.03	31.98	37.05
26/09/2018	26.22	19.17	35.34	37.4
27/09/2018	17.01	14.34	27.04	32.37
28/09/2018	20.56	16.63	30.24	30.97

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

The file can be downloaded here

https://drive.google.com/file/d/1G6K4jiLwNjLx4bllMxgmudJyGFCjPxTa/view?usp=sharing

 

[Aladin Akyurek]

Book1
	A	B	C	D	E
1	Date	NY temperature	LA temperature	Price	Nextday price
2	2/7/2018	32.22	26.91	49.92	69.88
3	3/7/2018	32.85	28.51	48.81	54.3
4	5/7/2018	30.69	24.58	53.43	43.77
5	6/7/2018	28.92	23.16	30.02	35.19
6	9/7/2018	27.74	25.52	40.59	34.6
7	10/7/2018	32.22	26.98	44.87	42.53
8	11/7/2018	31.08	24.13	42.01	38.76
9	12/7/2018	28.51	23.96	34.69	36.94
Sheet1

Book1
	A	B	C	D	E	F
1	NY	29
2	LA	26
3	5
4	Idx	Date	NY temperature	LA temperature	Price	Nextday price
5	1	2/7/2018	32.22	26.91	49.92	69.88
6	2	3/7/2018	32.85	28.51	48.81	54.3
7	3	5/7/2018	30.69	24.58	53.43	43.77
8	6	10/7/2018	32.22	26.98	44.87	42.53
9	7	11/7/2018	31.08	24.13	42.01	38.76
Sheet2

In A3 control+shift+enter, not just enter:

=SUM(IF((Sheet1!B2:B9>=B1)+(Sheet1!C2:C9>=B2),1))

In A5, control+shift+enter, not just enter, and copy down:

=IF(ROWS($1:1)>$A$3,"",SMALL(IF((Sheet1!$B$2:$B$9>=$B$1)+(Sheet1!$C$2:$C$9>=$B$2),ROW(Sheet1!$A$2:$E$9)-ROW(INDEX(Sheet1!$A$2:$E$9,1,1))+1),ROWS($1:1)))

In B5 just enter, copy across, and down:

=IF($A5="","",INDEX(Sheet1!$A$2:$E$9,$A5,MATCH(B$4,Sheet1!$A$1:$E$1,0)))

 

[lezawang]

Thank you so much for all your help. I will go through them now to understand them. Just a question. Can that be solved by using pivot table or not possible with pivot tables? Thank you so much once again.

 

[Aladin Akyurek]

Thank you so much for all your help. I will go through them now to understand them. Just a question. Can that be solved by using pivot table or not possible with pivot tables? Thank you so much once again.

Book1
	A	B	C	D	E	F
1	Date	NY temperature	LA temperature	Price	Nextday price	CRIT
2	2/7/2018	32.22	26.91	49.92	69.88	TRUE
3	3/7/2018	32.85	28.51	48.81	54.3	TRUE
4	5/7/2018	30.69	24.58	53.43	43.77	TRUE
5	6/7/2018	28.92	23.16	30.02	35.19	FALSE
6	9/7/2018	27.74	25.52	40.59	34.6	FALSE
7	10/7/2018	32.22	26.98	44.87	42.53	TRUE
8	11/7/2018	31.08	24.13	42.01	38.76	TRUE
9	12/7/2018	28.51	23.96	34.69	36.94	FALSE
Sheet1

In F2 enter and copy down:

=(B2>=29)+(C2>=26)>0

Book1
	A	B	C	D	E
1	CRIT	TRUE
2
3	Row Labels	Sum of NY temperature	Sum of LA temperature	Sum of Price	Sum of Nextday price
4	7-Feb	32.22	26.91	49.92	69.88
5	7-Mar	32.85	28.51	48.81	54.3
6	7-May	30.69	24.58	53.43	43.77
7	7-Oct	32.22	26.98	44.87	42.53
8	7-Nov	31.08	24.13	42.01	38.76
9	Grand Total	159.06	131.11	239.04	249.24
10
Sheet5

 

[lezawang]

Many thanks. I will try to understand this as well. Thank you so much.

 

[Aladin Akyurek]

You are welcome.