find a value based on 2 cells within a range

[SCNCKS1]

Hi - I hope someone can help, I have the following data and I need to dynamically return the rate associated with the rate_type and year/quarter. Dynamically, the date will continue to grow both on the rate table and the hour table. I have been trying the lookup and switch function, but i can't get the code to work correctly.

Hour Table

Year/Quarter	Rate_Type	Hours	Rate
2019 Q4	Item 1	10
2020 Q1	Item 2	10
2020 Q2	Item 3	10
2020 Q3	Item 4	10
2020 Q4	Item 5	10
2021 Q1	Item 6	10
2021 Q2	Item 7	10
2019 Q4	Item 8	10
2020 Q1	Item 9	10
2020 Q2	Item 10	10
2020 Q3	Item 11	10
2020 Q4	Item 12	10
2021 Q1	Item 13	10
2021 Q2	Item 14	10
2019 Q4	Item 15	10
2020 Q1	Item 16	10

Rate Table

Rate_Type	2019 Q4	2020 Q1	2020 Q2	2020 Q3	2020 Q4	2021 Q1	2021 Q2
Item 1	$20.00	$20.20	$20.40	$20.61	$20.81	$21.02	$21.23
Item 2	$20.40	$20.60	$20.81	$21.02	$21.23	$21.44	$21.66
Item 3	$21.00	$21.21	$21.42	$21.64	$21.85	$22.07	$22.29
Item 4	$21.42	$21.63	$21.85	$22.07	$22.29	$22.51	$22.74
Item 5	$22.00	$22.22	$22.44	$22.67	$22.89	$23.12	$23.35
Item 6	$22.44	$22.66	$22.89	$23.12	$23.35	$23.58	$23.82
Item 7	$23.00	$23.23	$23.46	$23.70	$23.93	$24.17	$24.41
Item 8	$23.46	$23.69	$23.93	$24.17	$24.41	$24.66	$24.90
Item 9	$24.00	$24.24	$24.48	$24.73	$24.97	$25.22	$25.48
Item 10	$24.48	$24.72	$24.97	$25.22	$25.47	$25.73	$25.99
Item 11	$25.00	$25.25	$25.50	$25.76	$26.02	$26.28	$26.54
Item 12	$25.50	$25.76	$26.01	$26.27	$26.54	$26.80	$27.07
Item 13	$26.00	$26.26	$26.52	$26.79	$27.06	$27.33	$27.60
Item 14	$26.52	$26.79	$27.05	$27.32	$27.60	$27.87	$28.15
Item 15	$27.00	$27.27	$27.54	$27.82	$28.10	$28.38	$28.66
Item 16	$27.54	$27.82	$28.09	$28.37	$28.66	$28.94	$29.23

[Ritaf1983 · Accepted solution]

Hן @SCNCKS1 
In order to connect the tables, you need to convert your rates table into a longitudinal table first.
Using unpivot in PQ :

After this your rates table will look as on pict :

From this stage ypu can just merge it based on item and quarter :

Link to sample file 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[SCNCKS1]

Thank you!