Using Multiple Tables to Match a Date Between Two Other Dates

All,

I have attached my workbook that I need help with and it has the desired outcome. Basically, I'm looking to use the following information for both employees and managers: Trip ID, Start Date, End Date, and Location ID.... I have already written a formula that pulls each trip for the specified employee, but not I want to cross check the start date, end date, and location ID with the start date, end dates, and location IDs of the managers. My desired result is to show the rows that a manager was in the same exact location at a date between the start and end dates of the employee being in that location. I will use a vlookup function in order to look up all the information to the right of the TripID, so all I need is to find the TripID that corresponds. Thank you so much for your help!

Trey

View attachment Example Help.xlsx

I'm also opened to adding a column in the manager's table titled "FIT" where you use an if statement to say "YES" if they do fit within the dates with the same location or "NO" if they don't fall in between the dates and don't have the same location.... I have done something like this, but considering my actual example has over 100K rows, some employees have at least 60 unique rows, which means I'd have to repeat my if statement 60+ times with different numbers attached... let me know if you can think of a better way for this strategy or even better, if you can figure out from my first post without adding any columns. Thanks!

Bump

Let's give this a try....

First let's add a helper column to your Manager's tab (you can hide this column if you want afterwards)...

in H3 enter formula: =OR(COUNTIFS(Summary!$E$7:$E$9,Managers!G3,Summary!$C$7:$C$9,">="&Managers!E3),COUNTIFS(Summary!$E$7:$E$9,Managers!G3,Summary!$D$7:$D$9,">="&Managers!F3))

copied down.

Now in Summary Sheet G3 enter this Array* formula:

=INDEX(Managers!B$3:B$7,SMALL(IF(Managers!$H$3:$H$7=TRUE,ROW(Managers!$B$3:$B$7)-ROW(Managers!$B$3)+1),ROWS(G$3:G3)))

copied down and across. (Note: You may have to change format in the date columns for dates to appear in appropriate spots).

Array* formulas require you to confirm with CTRL+SHIFT+ENTER not just ENTER, then copy down and/or across.

This worked perfectly!! Thank you so much

I may have just found a mistake in the formula, but I think it has to do with your note. What do you mean when you say, "You may have to change format in the date columns for dates to appear in appropriate spots"?

What is the "mistake" you are experiencing?

My comment means that you will have to format columns J and K as Date, so that instead of a number like 37988 you get an actual date like 1/2/2004.