Look up in a range of dates if fall between two dates and result in a value from range

[stearno]

I have two worksheets.

Worksheet 1: lists all the news events that happened (News). In the A column is the mm/dd/yyyy hh:mm

Worksheet 2: has the list which contains an Open date and a Close date on each line. The format is the same, which is mm/dd/yyyy hh:mm. The formulas will be in this worksheet.

I need to write two formulas to accomplish 2 things:

o find if there is a news event exists no more than x minutes before the opening date and time in the list.
o find if there is a news event between the opening and closing date and time. If yes, then result the letter in column G of the News worksheet of the item that is closest to the opening date/time in worksheet 2.

I appreciate any guidance/advice on how to do this? Thanks.

-Stearno

[JeteMc]

Please try the following:
For column Y on the EURUSD - Past Trades Analysis sheet:

Please Login or Register  to view this content.

Note that x is the number of minutes before the opening date and time.
For column Z:

Please Login or Register  to view this content.

Let us know if you have any questions.

[stearno]

JeteMc,
Thank you for your suggestions. The second formula works. The only problem with this formula is if it doesn't find it to be true, it results in #Num!. But I can work with that.

For the first formula, it yeilds #Num! in all rows. When I break the formula apart, it happens inside the Aggregate formula. And it this part of the formula yeilds "0", which I believe is causing the #Num!

Please Login or Register  to view this content.

[JeteMc]

Here is a copy of the file with both formulas applied.
Let us know if you have any questions.

[stearno]

That is great. Works well. Thank you so much for your help!

[JeteMc]

You're Welcome and thank you for the feedback. If that answers all of your questions, please take a moment to mark the thread as 'Solved' using the thread tools menu above your fist post. I hope that you have a blessed day.