Two lists, if same dates and inbetween start and end times

[stearno]

I have two worksheets in the same workbook.

On Worksheet 1, I have a date in Column B and time in Column C.

On Worksheet 2, I have a date in Column A and start time in Column C and end time in Column D.

What I am trying to do:
1. In worksheet 1. Compare Worksheet 1's date to the list of dates in worksheet 2. (there is going to multiple of the same date in Worksheet 2).
2. If the dates are the same, then I need it compare the times. I need to know if Worksheet 1's time is equal to or in between the Worksheet 2's Start and End Time.
3. If it is in between, then I want it to return the value of Worksheet 2: Column E's value of that same row.
4. If it is not in between, then it should go to the next matching date in the list (as there are multiple of the same dates in worksheet 2).
5. If there is not another matching date, then return blank.

I have googled and since this is a multi-faceted request, I have found it hard to figure it out.

I appreciate any help / guidance.

-Stearno

[JohnTopley]

Try

in E2 of Sheet1 (assuming data starts in row 2 on both sheets)

=IFERROR(INDEX(Sheet2!$E$2:$E$100,SMALL(IF((Sheet2!$A$2:$A$100=Sheet1!$B3)*(Sheet2!$C$2:$C$100<=Sheet1!$C3)*(Sheet2!$D$2:$D$100>=Sheet1!$C3),ROW(Sheet1!$B$2:$B$100)-ROW($B$2)+1,""),1)),"")

Enter with Ctrl+Shift+Enter

Assumes only one valid match on date/time for a given date

[stearno]

John,
Thanks for the formula.

First, I copied the formula into Sheet1 E2 cell in the text field. I hit Ctrl + Shift + Enter. I then highlighted the cells below the cell where I just put the array, and clicked Fill Down on the Editing section of the Home tab.

I put It is not producing any information in the field. I have attached my sheet so as to make this easier.

Second, I would like to learn from what you did as well.

Let me explain my understanding of your formula to see if I have understood it correctly. The logic says:
if the cell in Sheet1 Column B is = to the first cell that it comes across in array Sheet2!A2:A42939
if it finds the same date, then if the cell from the same row in array Sheet2!C2:C42939 is < Sheet1 Column C and Sheet2!D2:D42939 > Sheet1 Column C
then return the row from Sheet1 - the starting cell number.

Note: I changed it from <= and >= to just < and >. Because I want to include if it is equal to. If I must be in between the start and end time, I also want to consider it in between when it is also equal to. Am I correct in understanding?

Two questions about the formula:
1) It seems to me that it if the date matches, the Sheet1 time is in between the Column C and Column D times, then the formula returns the number of rows away from Sheet1 Column B2 where it found this match. Am I correct?
Instead, what I want to return is shown in row 6 on Sheet1. If the 3 conditions are correct, I want it to return Sheet 2 Column E into the cell of Sheet 1 Column E.

Thanks,
Stearno

[JohnTopley]

No attachment.

to upload file click "Go Advanced" then scroll down to "Manage Attachments"

[stearno]

Wow, thats a surprise. I now know it was because it was 'too big'. To get around that, here it is: https://dl.dropboxusercontent.com/u/...-myfxbook.xlsx

[JohnTopley]

Please post a cut-down version here as many members will not visit file-hosting sites.

[stearno]

Sorry. Here you go.

[JohnTopley]

Two changes:

in Column C on Sheet1 to get the correct time (your times were incorrect)

=MOD(B2,1)

copy down

In Column E

=IFERROR(INDEX(Sheet2!$E$2:$E$157,SMALL(IF((Sheet2!$A$2:$A$157=INT(Sheet1!$B2))*(Sheet1!$C2>=Sheet2!$C$2:$C$157)*(Sheet1!$C2<=Sheet2!$D$2:$D$157),ROW(Sheet2!$B$2:$B$157)-ROW($B$2)+1,""),1)),"")

Enter with Ctrl+Shift+Enter

Your date in B is a date /time so we need to "remove" the time when comparing.

[stearno]

That worked! THank you so much for your valuable help!

-Stearno