Lookup the corresponding value for a match when a cell value falls between cell dates

[eagerexceler]

Hello, I'm trying to create a dynamic formula to dropdown, and I haven't been able to get an index match formula to work.

In sheet1 I have a list of accounts with common 'Provider' names in column A. Column B contains the corresponding 'Service Date'.
Sheet 2 has a list of rates for each common 'Provider' names in column B, and a' Service Start Date' and 'Service End Date' in columns D and E, respectively. Column C has the corresponding rate for each provider for each timeframe.

I need a formula to put in column C of sheet 1 that matches the 'Provider' name in column A of sheet1 with that of column B of sheet 2 when the date in column B of sheet1 falls within the respective start and ends dates of sheet2, and returns the corresponding rate.

Unfortunately I can't upload my sheets using XL2BB on my work computer, sorry.

 

[myall_blues]

Here's one solution

Book1
	A	B	C
1	Provider	Service Date	Rate
2	A	15/01/2023	175
3	B	2/05/2023	225
4	C	13/10/2023	275
5	D	1/03/2024	350
6	E	1/06/2024	375
Sheet1

Cell Formulas
Range	Formula
C2:C6	C2	=SUMPRODUCT((Sheet2!$B$2:$B$10=$A2)*($B2>=Sheet2!$D$2:$D$10)*($B2<=Sheet2!$E$2:$E$10)*Sheet2!$C$2:$C$10)

Book1
	B	C	D	E
1	Provider	Rate	Service Start	Service End
2	A	175	1/01/2023	28/02/2023
3	A	200	1/03/2023	30/04/2023
4	B	225	1/05/2023	30/06/2023
5	B	250	1/07/2023	31/08/2023
6	C	275	1/09/2023	31/10/2023
7	C	300	1/11/2023	31/12/2023
8	D	325	1/01/2024	29/02/2024
9	D	350	1/03/2024	30/04/2024
10	E	375	1/05/2024	30/06/2024
Sheet2

 

[eagerexceler]

Perfect. I was indeed making it too hard. Sumproduct is underrated and I forgot about it!

Thanks friend.

 

[myall_blues]

Great news. Thanks for the feedback.