Excel - Find nearest date based on criteria

Question

I was hoping if somebody could give me a light to an Excel problem I'm trying to solve.

I have two tables. One called SENSOR and the other one CARD

1 - Table SENSOR has a date/time in one column and employee ID on the other 2 - Table CARD has a date/time in one column and employee ID on the other

The only exact match is the employee ID. The date/time from table SENSOR is not an exact match from table CARD (it is usually a couple of minutes plus or minus). What I need to do is to return on cell C1 the date/time column from table CARD that is the nearest time found in date/time column from SENSOR table.

TABLE SENSOR:

Column A: DATE/TIME
11/3/17 10:48
11/3/17 16:02
11/10/17 7:09
11/13/17 20:58
11/15/17 21:24
11/16/17 11:32

Column B: EMPLOYEE ID
7164
7165
7166
7163
7167
7163

TABLE CARD:

COLUMN I: DATE/TIME
11/3/17 16:05
11/10/17 7:10
11/13/17 21:04
11/15/17 21:30
11/16/17 11:40
11/3/17 10:45

COLUMN J: EMPLOYEE ID
7165
7166
7163
7167
7163
7164

Here is what I'm trying to do (It actually works in some of them, but in some others it just doesn't work and I can't understand why):

C1 =INDEX(I$2:I$285,MATCH(MIN(ABS(I$2:I$285-A16)),(J$2:J$285=B16)*ABS(I$2:I$285-A16),0))

Thank you guys

Answer 1

Since you mentioned TABLES I used TABLES and structured references. Makes things a bit easier, in the long run. But you should be able to translate to direct references, if you need to.

Here is the formula:

=LOOKUP(2,1/(AGGREGATE(15,6,(ABS(([@[EMPLOYEE ID]]=CARD[EMPLOYEE ID])*
CARD[DATE/TIME]-[@[DATE/TIME]])),1)=ABS(([@[EMPLOYEE ID]]=CARD[EMPLOYEE ID])*
CARD[DATE/TIME]-[@[DATE/TIME]])),CARD[DATE/TIME])

• compare employee ID in Sensor with employee ID in card
• return an array of the matching date times or zero's (if not match)
• subtract from that the date/time in the current row and return the ABS value
• using LOOKUP, return the last time that is in the same position as the minimum (ABS) difference.
• the AGGREGATE function is used to avoid dealing with error values. It also allows avoiding using ctrl+shift+enter for array formula.