Look up value between two dates

[RDF25087]

Hi all -

Hopefully this is fairly simple - though not for me...

I have a list of products and their price changes over time, e.g.

Product	From	To	Cost
8965	01.01.2020	31.12.2020	195.4
8965	01.01.2021	31.03.2021	206.75
8965	01.04.2021	31.12.2021	211.33
8965	01.01.2022	31.12.9999	225.63
8974	01.10.2021	31.12.2021	261.84
8974	01.01.2022	31.03.2022	275.46
8974	01.04.2022	31.12.9999	283.33

I need to append the "Cost" value to my main data where the "Date" value falls between the "From" and "To" dates in the table above:

Date	Product	Expected Result
01.01.2022	8974	275.46
26.10.2021	8965	211.33

So, in the example above for product 8965, because the date (26.10.2021) falls between 01.04.2021 and 31.12.2021 in the top table I need to append the value of 211.33.

Makes sense?

Any help greatly appreciated, as always.

RDF

[IraWatt · Accepted solution]

Hey @RDF25087,

Here is a simple way to do this:

Any questions or issues please ask :)
HTH!
Ira

[binu_acs]

@RDF25087 one way of dong this

[RDF25087]

Hi both -

Thank you both for your solutions. I ran @binu_acs in my workflow and it worked a treat.

Regards

RDF