Nested VLOOKUP + Date Comparison Error

JacksonPorter

New Member
Joined
Jun 19, 2023
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Greetings Excel Wizards,

I have a bit of a complicated request that I am pursuing, and after butting my head up against Google Searches for the better part of a day, I was hoping I could get some help with my situation. I am a complete Excel noob and prefer to do this if at all possible with formulae instead of VBA, but I'm willing to try anything since, given the size of the task, I cannot do this manually.

I have multiple events each with a specific ID, a separate employee ID, and a date of occurrence on one sheet. On another sheet, each employee ID is associated on different rows with varying dates of service. What I want to find is the closest prior date of service relative to date of occurrence per employee associated with the event. See below:

Sheet 1:
Sheet 1.png

Sheet 2 (dates not given for the rest of the employees; just showing that the amount of dates of service varies from employee to employee):
Sheet 2.png

My thought process went like this: 1) from Sheet 1, VLOOKUP employee ID from event ID (note that employee ID is not unique for each event, thus requiring this step); 2) on Sheet 2, use employee ID to produce a list of their service dates; 3) VLOOKUP date of occurrence from Sheet 1; and 4) from the list of service dates, find the closest prior date of service relative to date of occurrence. In this case, event ID 1 gives employee ID 11111 that has dates 1/2/02, 1/1/03, 1/1/04, and 1/1/05; event ID 1 has 1/2/03 date of occurrence, so formula spits out 1/1/03 as closest employee date of service.

It's really generating the list of dates per employee that's tripped me up since I can't figure out a way to automate it without using concatenation, but then that cannot be used for formulaic comparison to the date of occurrence. Any help is greatly appreciated--thank you all so much.
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
For Sheet2 like this:
Book2
AB
1Employee IDDate of Service
2111111/2/2002
3111111/1/2003
4111111/1/2004
5111111/1/2005
6222222/2/2003
7222222/2/2004
8222222/2/2005
Sheet2

Here is one approach:
Book2
ABCDEF
1Event IDEmployee IDDate of OccurrenceEvent IDClosest Prior Date of Service
21111111/2/200322/2/2004
32222222/3/200411/1/2003
43333333/4/2005
54444444/5/2006
65444445/6/2007
Sheet1
Cell Formulas
RangeFormula
F2:F3F2=MAX(FILTER(Sheet2!$B$2:$B$8,(Sheet2!$A$2:$A$8=XLOOKUP(E2,$A$2:$A$6,$B$2:$B$6))*(Sheet2!$B$2:$B$8<=XLOOKUP(E2,$A$2:$A$6,$C$2:$C$6))))
 
Upvote 1
Solution
KRice, you are my savior. It worked like a charm, thank you so much! I would have never been able to string such an elegant formula together.
 
Upvote 0
I'm happy to help...and glad it's working for you.
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top