Need help with lookup between two workbooks using multiple criteria

[Redd_Dayspring]

Hello. I have been wracking my brain with this (not hard, not the greatest with Excel).
I have two workbooks.
I need to use the Employee ID (B2) and Transaction Date (C2) from Workbook A as the criteria to search and match up with the Proposed Start and Propose End date range in Workbook B, columns J:K.
Once the date is found within the rate, I need to locate and return the corresponding proposed rate in column L and place it into Workbook A.

Hopefully this makes sense..
Is this feasible?

 

[ExceLoki]

the transaction dates in your example are not in the range of dates for those employees on the other sheet.
but i added some example to show you how you can do this.

Book1
	A	B	C	D	E	F
1	Employee	Employee ID	Transaction Date	Report Date	Hire Date	Rate
2	Smith, John	456	3/3/2020	3/3/2020	9/18/2014	#N/A
3	Adams, John	789	10/30/2019	10/31/2019	3/16/1998	#N/A
4	Washington, George	1023	7/20/2020	7/20/2020	4/15/2020	#N/A
5	Smith, John	456	7/3/2020	3/3/2020	9/18/2014	75.70
6	Adams, John	789	10/30/2020	10/31/2019	3/16/1998	72.08
7	Adams, John	789	10/30/2022	10/31/2019	3/16/1998	74.78
Sheet1

Cell Formulas
Range	Formula
F2:F7	F2	=INDEX(Sheet2!$F$2:$F$8,MATCH(1,((Sheet2!$A$2:$A$8=B2)*(Sheet2!$E$2:$E$8>=C2)*(Sheet2!$D$2:$D$8<=C2)),0))

-------------------

Book1
	A	B	C	D	E	F
1	Employee ID	Legal Name	Current	Proposed Start	Proposed End	Proposed
2	456	John Smith	74.47	3/23/2020	5/22/2023	75.70
3	789	John Adams	71.10	3/23/2020	4/5/2021	72.08
4	789	John Adams	72.08	4/5/2021	2/7/2022	72.68
5	789	John Adams	72.68	2/7/2022	4/4/2022	77.00
6	789	John Adams	77.00	4/4/2022	4/18/2022	77.66
7	789	John Adams	77.66	4/18/2022	12/22/2022	74.78
8	789	John Adams	74.78	12/22/2022	5/22/2023	74.78
Sheet2

 

[Redd_Dayspring]

Wow...this is exactly what I needed. It worked perfectly! Thank you so much.

 

[ExceLoki]

no prob, happy to help