Lookup formula using multiple criteria, including ranges in two columns

Hi there
New forum member here so first time posting

I have been struggling to get a formula to work and hoping someone can help me?

I have a workbook with two tabs. The first tab (rate) contains a list of employees and their hourly rates within a given date range (start date is in one column and end date is in the next column). The second tab (data) contains employee data and a list of dates that I need hourly rates for.

What I need to do is determine what hourly rate was applicable for the single date in the range on the "data" tab, so for example if the date I'm looking at was 02/11/2018 for a particular employee and the date range was starting 25/10/2018 and finishing 30/06/2019, I need the hourly rate for that employee within that range.

I've attached a sample workbook to hopefully make this easier to follow

Thanks for your help!

worksheet name : data

Try Cell C2 formula , drag down

Formula:

Please Login or Register  to view this content.

Another option in cell C2 and copy down:

=FILTER(rate!$D$2:$D$22,(rate!$A$2:$A$22=B2)*(rate!$B$2:$B$22<=A2)*rate!$C$2:$C$22>=A2)

Thanks everyone. I'll give them a shot and come back if I have any more questions.

Appreciate you coming back to me so quickly!

@lynnsharkey You're Welcome. Glad to help . Thank You for the feedback