Date Lookup

[kparadise]

Hello,

[Sheet 1] as two columns. Column A [ID] and Column TIME
[Sheet 2] as two columns. Column A [ID] and Column TIME

[Sheet 2] holds all historical date for every ID and every occurrence in time that the ID performed a specific action.

1. What I am trying to create in [Sheet 1].[Column C] is to enter a formula to ask what is the closest date/time in Sheet 2 without being greater than date/time in Sheet 1 for each ID / TIME combination found on Sheet 1.

2. What I am trying to create in [Sheet 1].[Column D] is to enter a formula to ask what is the closest date/time in Sheet 2 with is greater than date/time in Sheet 1 for each ID / TIME combination found on Sheet 1.


[Sheet 1]
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]ID
[/TD]
[TD="align: center"]TIME
[/TD]
[TD="align: center"]Closest Prior to Column B
[/TD]
[TD="align: center"]Closest After Column B
[/TD]
[/TR]
[TR]
[TD]123
[/TD]
[TD]12/13/2017 8:15 AM
[/TD]
[TD]10/15/2017 3:33 PM
[/TD]
[TD]12/13/2017 8:20 AM
[/TD]
[/TR]
[TR]
[TD]123
[/TD]
[TD]12/25/2017 11:45 PM
[/TD]
[TD]12/13/2017 8:20 AM
[/TD]
[TD]12/26/2017 6:45 AM
[/TD]
[/TR]
[TR]
[TD]789
[/TD]
[TD]12/1/2017 9:30 AM
[/TD]
[TD]12/1/2017 8:30 AM
[/TD]
[TD]12/1/2017 10:30 AM
[/TD]
[/TR]
</tbody>[/TABLE]

[Sheet 2]
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]ID
[/TD]
[TD="align: center"]TIME
[/TD]
[/TR]
[TR]
[TD]123
[/TD]
[TD]12/13/2017 8:20 AM
[/TD]
[/TR]
[TR]
[TD]123
[/TD]
[TD]12/26/2017 6:45 AM
[/TD]
[/TR]
[TR]
[TD]123
[/TD]
[TD]8/20/2017 10:25 PM
[/TD]
[/TR]
[TR]
[TD]123
[/TD]
[TD]7/15/2017 10:50 AM
[/TD]
[/TR]
[TR]
[TD]123
[/TD]
[TD]10/15/2017 3:33 PM
[/TD]
[/TR]
[TR]
[TD]789
[/TD]
[TD]12/1/2017 10:30 AM
[/TD]
[/TR]
[TR]
[TD]789
[/TD]
[TD]12/1/2017 8:30 AM
[/TD]
[/TR]
[TR]
[TD]789
[/TD]
[TD]12/2/2017 10:45 PM
[/TD]
[/TR]
</tbody>[/TABLE]

 

[dreid1011]

Perhaps these:

Col C
=MAXIFS(Sheet2!$B$2:$B$9,Sheet2!$A$2:$A$9,A2,Sheet2!$B$2:$B$9,"<"&B2)

Col D
=MINIFS(Sheet2!$B$2:$B$9,Sheet2!$A$2:$A$9,A2,Sheet2!$B$2:$B$9,">"&B2)

If you have Excel 2016. May need some alteration for older versions.

For older versions:

Col C
=MAX(IF(Sheet2!$A$2:$A$9=A2,IF(Sheet2!$B$2:$B$9<B2,Sheet2!$B$2:$B$9)))<B2,Sheet2!$B$2:$b$9)))

Col D
=MIN(IF(Sheet2!$A$2:$A$9=A2,IF(Sheet2!$B$2:$B$9>B2,Sheet2!$B$2:$B$9)))

These are entered as array formulas with CTRL+SHIFT+ENTER, not just ENTER.</B2,Sheet2!$B$2:$b$9)))

 

[kparadise]

Second one works good! thank you. wow...so easy.

 

[dreid1011]

Glad to be of service. You're welcome.