VLOOKUP between two dates in Google Sheets

Question

I'm having trouble with a vlookup between two dates. This the case:

In my first tab I have the following data:

You can see that person "Stephen Chesson" has another profile from 01/08/2022.

In my second tab I have the following data:

Now I would like to automatically fill in the profile based on the data in the first tab.

The result should be that in the second tab the following data is presented:

• Cell C2 should be "Intern Developer"
• Cell C3 should be "Developer"

How can I make this happen?

The google sheet can be found here.

Answer 1

Use filter(), like this:

=iferror( filter('The Team'!D$2:D, len('The Team'!A$2:A), 'The Team'!A$2:A = A2, 'The Team'!B$2:B <= B2, B2 <= 'The Team'!C$2:C) )