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

[TABLE="class: grid, width: 560"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD]NY temperature[/TD]
[TD]LA temperature[/TD]
[TD]Price[/TD]
[TD]Nextday price[/TD]
[/TR]
[TR]
[TD="align: right"]02/07/2018[/TD]
[TD="align: right"]32.22[/TD]
[TD="align: right"]26.91[/TD]
[TD="align: right"]49.92[/TD]
[TD="align: right"]69.88[/TD]
[/TR]
[TR]
[TD="align: right"]03/07/2018[/TD]
[TD="align: right"]32.85[/TD]
[TD="align: right"]28.51[/TD]
[TD="align: right"]48.81[/TD]
[TD="align: right"]54.3[/TD]
[/TR]
[TR]
[TD="align: right"]05/07/2018[/TD]
[TD="align: right"]30.69[/TD]
[TD="align: right"]24.58[/TD]
[TD="align: right"]53.43[/TD]
[TD="align: right"]43.77[/TD]
[/TR]
[TR]
[TD="align: right"]06/07/2018[/TD]
[TD="align: right"]28.92[/TD]
[TD="align: right"]23.16[/TD]
[TD="align: right"]30.02[/TD]
[TD="align: right"]35.19[/TD]
[/TR]
[TR]
[TD="align: right"]09/07/2018[/TD]
[TD="align: right"]27.74[/TD]
[TD="align: right"]25.52[/TD]
[TD="align: right"]40.59[/TD]
[TD="align: right"]34.6[/TD]
[/TR]
[TR]
[TD="align: right"]10/07/2018[/TD]
[TD="align: right"]32.22[/TD]
[TD="align: right"]26.98[/TD]
[TD="align: right"]44.87[/TD]
[TD="align: right"]42.53[/TD]
[/TR]
[TR]
[TD="align: right"]11/07/2018[/TD]
[TD="align: right"]31.08[/TD]
[TD="align: right"]24.13[/TD]
[TD="align: right"]42.01[/TD]
[TD="align: right"]38.76[/TD]
[/TR]
[TR]
[TD="align: right"]12/07/2018[/TD]
[TD="align: right"]28.51[/TD]
[TD="align: right"]23.96[/TD]
[TD="align: right"]34.69[/TD]
[TD="align: right"]36.94[/TD]
[/TR]
</tbody>[/TABLE]

.
.
.

[TABLE="class: grid, width: 560"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD="align: right"]24/09/2018[/TD]
[TD="align: right"]19.06[/TD]
[TD="align: right"]17.15[/TD]
[TD="align: right"]23.27[/TD]
[TD="align: right"]31.95[/TD]
[/TR]
[TR]
[TD="align: right"]25/09/2018[/TD]
[TD="align: right"]24.55[/TD]
[TD="align: right"]20.03[/TD]
[TD="align: right"]31.98[/TD]
[TD="align: right"]37.05[/TD]
[/TR]
[TR]
[TD="align: right"]26/09/2018[/TD]
[TD="align: right"]26.22[/TD]
[TD="align: right"]19.17[/TD]
[TD="align: right"]35.34[/TD]
[TD="align: right"]37.4[/TD]
[/TR]
[TR]
[TD="align: right"]27/09/2018[/TD]
[TD="align: right"]17.01[/TD]
[TD="align: right"]14.34[/TD]
[TD="align: right"]27.04[/TD]
[TD="align: right"]32.37[/TD]
[/TR]
[TR]
[TD="align: right"]28/09/2018[/TD]
[TD="align: right"]20.56[/TD]
[TD="align: right"]16.63[/TD]
[TD="align: right"]30.24[/TD]
[TD="align: right"]30.97[/TD]
[/TR]
</tbody>[/TABLE]

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.