...
Filter rows that have column “Value” = the lookup key AND:
The value of “From” >= Quote Start Date,
OR “From” is empty.
Ordering the resulting rows ascendingly
Notes: empty value is always at the end of the order list.
NOTES: The standard Date format for column “From” is dd/MM/yyyy
GIVEN we have the VehicleAge lookup as below.
...
WHEN the lookup key is 3, we will have the following rows satisfy the condition:
...
We THEN we can have scenarios as below:
If Quote Start Date is from 0114/0403/2022 (1st Apr 14th March 2022) to 31/12/2022, the 1st row will be picked, and the factor value for VehicleAge should be 1.5 for all covers.
If Quote Start Date is from 01/01/2023, the 2nd row will be picked, and the factor value for VehicleAge should be 2 for all covers.
If Quote Start Date is before 14/03/2022, the 3rd row will be picked, and the factor value for VehicleAge should be 1 for all covers.