Find the value through two lookup inputs

[kenil gala]

Query - I have Hourly stock prices in first worksheet - I have 2 problems relating to it
I want to convert this hourly data into a daily data

1. I want date in "Daily" worksheet as in sequence to data i.e. 1st date is 4th april 2022 and continued in sequence till 8th april
later market opens afetr 2 days on 11th april - I need the same date sequence as in hourly data worksheet

2. I have calculated "Maximum" and "Minimum" Values for a particular day from the "hourly data"
but, I also need time at which point that data occurred
I have filled the column manually for understanding

Thanks

[JohnTopley]

Try


in A3

=IFERROR(INDEX('Hourly Data'!$A$3:$A$122, MATCH(0, COUNTIF($A$1:A1, 'Hourly Data'!$A$3:$A$122), 0)), "")

Enter with Ctrl+Shift+Enter

in G3

=INDEX('Hourly Data'!$B$3:$B$122,AGGREGATE(15,6,ROW($A$1:$A$122)/(('Hourly Data'!$D$3:$D$122=Daily!$C3)*('Hourly Data'!$A$3:$A$122=Daily!$A3)),1))

in I3

=INDEX('Hourly Data'!$B$3:$B$122,AGGREGATE(15,6,ROW($A$1:$A$122)/(('Hourly Data'!$E$3:$E$122=Daily!$D3)*('Hourly Data'!$A$3:$A$122=Daily!$A3)),1))

Is your profile correct ? If you have a later version on Excel: if so, please update your profile as answers do depend on Excel version you have.

[kenil gala]

Thanks, But Date is getting repeated twice in Column A

4April2022 - 2 times
5April2022 - 2 times
6April2022 - 2 times.... and so on

Please Revert on this.
Thanks

[JohnTopley]

try

=IFERROR(INDEX('Hourly Data'!$A$3:$A$122, MATCH(0, COUNTIF($A$2:A2, 'Hourly Data'!$A$3:$A$122), 0)), "")

[kenil gala]

It worked.
Thank You

[JohnTopley]

You're welcome.

Could you please mark as SOLVED (see "Thread Tools" at top of sheet").

Thank you.