Forum Discussion

SCNCKS1's avatar
SCNCKS1
Icon for Helper I rankHelper I
3 years ago
Solved

find a value based on 2 cells within a range

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/QuarterRate_TypeHoursRate
2019 Q4Item 110 
2020 Q1Item 210 
2020 Q2Item 310 
2020 Q3Item 410 
2020 Q4Item 510 
2021 Q1Item 610 
2021 Q2Item 710 
2019 Q4Item 810 
2020 Q1Item 910 
2020 Q2Item 1010 
2020 Q3Item 1110 
2020 Q4Item 1210 
2021 Q1Item 1310 
2021 Q2Item 1410 
2019 Q4Item 1510 
2020 Q1Item 1610 

Rate Table

Rate_Type2019 Q42020 Q12020 Q22020 Q32020 Q42021 Q12021 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
Need Help
  • Ritaf1983's avatar
    Ritaf1983
    3 years ago

    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.

2 Replies

  • Ritaf1983's avatar
    Ritaf1983
    Icon for Super User rankSuper User
    3 years ago

    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.