Hi, I am trying to perform a many to one lookup of hourly rates over a period of date ranges. The goal here is to extract the Rate Cost Rate column from Table 2 and put that into Table 1 based on the Person User Name and date criteria. For example, the rate on ADOE between 01/01/2023 - 03/31/2023 should be 40 but 04/01/2023 until 12/31/2099 should be escalated to 45. So Table 1 would properly stated as it is hard coded below. I've found index match to be a useful tool here but not with multiple criteria restrictions on the lookup table versus values table. Any feedback would be appreciated.
-Tim
Table 1:
Table 2:
-Tim
Table 1:
| Table 1 | 1/31/2023 | 2/28/2023 | 3/31/2023 | 4/30/2023 | 5/31/2023 | 6/30/2023 | 7/31/2023 | 8/31/2023 | 9/30/2023 | 10/31/2023 | 11/30/2023 | 12/31/2023 |
| A Doe | 40 | 40 | 40 | 45 | 45 | 45 | 45 | 45 | 45 | 45 | 45 | 45 |
| B Doe | ? | ? | ? | ? | ? | ? | ? | ? | ? | ? | ? | ? |
| C Doe | ? | ? | ? | ? | ? | ? | ? | ? | ? | ? | ? | ? |
Table 2:
| Person Active | Person Name | Person User Name | Rate Bill Rate | Rate Cost Rate | Rate Begin Date | Rate End Date |
| Y | Doe, A | ADOE | 0 | 40 | 1/1/2023 | 3/31/2023 |
| Y | Doe, A | ADOE | 0 | 45 | 4/1/2023 | 12/31/2099 |
| Y | Doe, B | BDOE | 0 | 70 | 1/1/2023 | 2/28/2023 |
| Y | Doe, B | BDOE | 0 | 72.5 | 3/1/2023 | 12/31/2099 |
| Y | Doe, C | CDOE | 0 | 85 | 1/1/2023 | 1/31/2023 |
| Y | Doe, C | CDOE | 0 | 95 | 2/28/2023 | 12/31/2099 |
