Date Lookup

kparadise

Board Regular
Joined
Aug 13, 2015
Messages
176
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]
ID
TIME
Closest Prior to Column B
Closest After Column B
123
12/13/2017 8:15 AM
10/15/2017 3:33 PM
12/13/2017 8:20 AM
123
12/25/2017 11:45 PM
12/13/2017 8:20 AM
12/26/2017 6:45 AM
789
12/1/2017 9:30 AM
12/1/2017 8:30 AM
12/1/2017 10:30 AM

<tbody>
</tbody>

[Sheet 2]
ID
TIME
123
12/13/2017 8:20 AM
123
12/26/2017 6:45 AM
123
8/20/2017 10:25 PM
123
7/15/2017 10:50 AM
123
10/15/2017 3:33 PM
789
12/1/2017 10:30 AM
789
12/1/2017 8:30 AM
789
12/2/2017 10:45 PM

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

dreid1011

Well-known Member
Joined
Jun 4, 2015
Messages
2,126
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)))
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,099,488
Messages
5,468,895
Members
406,620
Latest member
Gitani123

This Week's Hot Topics

Top