Look-up and return data in a certain cell according to multiple criteria

Hi
What and where should be your expected results?

Hi, belinda200
My expected results are values from the second sheet:
1) that correspond to the company ticker and day of an event from the first sheet
2) returns values for the following 30 days from the date of an event
I tried to represent it in the sample file (Sheet1), in the example of MGA.N, where event happens in 06.01.2010 and values are returned from the Sheet2 starting from 06.01.2010 and for the following 30 days.
I hope it gives some more explanation for my problem.
Thanks!

Hi
Try this in B3 and acrosS:

=IF(AND($A3-B$2<30,$A3-B$2>=0),AGGREGATE(14,6,(Sheet2!$B$2:$D$4502)/(Sheet2!$B$1:$D$1=Sheet1!B$1)/(Sheet2!$A$2:$A$4502=Sheet1!$A3),1),"")

Originally Posted by belinda200

Hi
Try this in B3 and acrosS:

=IF(AND($A3-B$2<30,$A3-B$2>=0),AGGREGATE(14,6,(Sheet2!$B$2:$D$4502)/(Sheet2!$B$1:$D$1=Sheet1!B$1)/(Sheet2!$A$2:$A$4502=Sheet1!$A3),1),"")

It definitely solves my problem! Thank you very much, really appreciate it. I've been mixing my had with some Index+Match stuff. Didn't know about Aggregate function!

You can definitely also use the INDEX/MATCH to get same results.

use this in B3 and across:
=IF(AND($A3-B$2<30,$A3-B$2>=0),INDEX(Sheet2!$B$2:$D$4502,MATCH(Sheet1!$A3,Sheet2!$A$2:$A$4502,0),MATCH(Sheet1!B$1,Sheet2!$B$1:$D$1,0)),"")