in my solution, i was referencing to column D & E in Sheet2, and only up to 45 rows. you didnt change that. anyway, it seems like my formula was flawed somehow. here's an amended one to paste in Sheet1 C2 & pasted down:
=IF(ISNA(LOOKUP(2,1/((Sheet2!$A$2:$A$4572=A2)*(Sheet2!$B$2:$B$4572>=B2-TIME(,30,))*(Sheet2!$B$2:$B$4572<B2)),Sheet2!$B$2:$B$4572)),"",LOOKUP(2,1/((Sheet2!$A$2:$A$4572=A2)*(Sheet2!$B$2:$B$4572>=B2-TIME(,30,))*(Sheet2!$B$2:$B$4572<B2)),Sheet2!$B$2:$B$4572))