Lookup value and date range from a Related Table

[ToddMate]

Hi,

I have a look up table that shows Items, a From and To date along with a price. This table will have the same Item name muliple times with From and To dates ending and starting after each other. (Never overlapping).

I then have a data table that has the item name (fish) and a date of purchase. 

The outcome i am after is to return the price that was correct as at the time of purchase. An example (in excel) is shown below.

Because the item Name occurs multiple times in the Lookup table and in the Data table i can't build a relationship that works. 

Any help would be appreciate (The values in green is what i am hoping to return)

Cheers Todd. 

[amitchandak · Accepted solution]

@ToddMate , A new column

New column in table 2 =
maxx(filter(Table1, Table1[Item Name] = table2[Item Name] && Table2[Date Purchase] >= Table1[from] && Table2[Date Purchase] <= Table1[to] ) Table1[Price])

[bourquejeff75]

Beautiful! Thank you for posting this solution. Will help in several instances with my clients.