Lookup of User ID with 2 criteria: 1) Item ID match in ranges 2) Closest date and time in the past.

[Letsdoexcel]

Good day, I have the following issue:

Table 1:
Column A: dateTime
Column B: Item ID

Table 2: (another worksheet)
Column D: dateTime 2
Column E: Item ID
Column F: User ID

I have to find the User ID for the related Item ID and dateTime 1 in Table 1 with the following criteria:

1. item ID from Column B = item ID from column E

2. Closest dateTime 2 to dateTime1 (dateTime2 must be always less than dateTime1, but the closest value. Basically it is the closest past date and time in relation to dateTime 2 of the same item ID.

Thank you in advance!

 

[Aladin Akyurek]

In B2 control+shift+enter, not just enter:

=INDEX($F$2:$F$10,MATCH(MAX(IF($E$2:$E$10=$B2,IF(ISNUMBER($D$2:$D$10),IF($D$2:$D$10 < $A2,$D$2:$D$10)))),IF($E$2:$E$10=$B2,IF(ISNUMBER($D$2:$D$10),IF($D$2:$D$10 < $A2,$D$2:$D$10))),0))

 

[Letsdoexcel]

Thank you a lot! This works perfectly!

 

[Aladin Akyurek]

Thank you a lot! This works perfectly!

You are welcome.