Having price history table on separate sheet is probably the best solution.



Price History sheet: columns ItemID, ItemPrice, DateFrom, DateTo, PriceNo
i.e given price is valid starting from DateFrom until another entry for same item with DateFrom later as current one. Column PriceNo enumerates prices for same item - use COUNTIFS() to count records with same ItemID and with DateFrm less than current one. Column DateTo uses SUMIFS() formula to return next DateFro for same item, or TODAY() when there is not any later entry. And you have to be sure there are no rows with same IdemID and DateFrom (you can use conditional formatting for this - format the font red bold for double rows).

To get right price, use SUMIFS() function (=SUMIFS(tPriceHistory[ItemPrice],tPriceHistory[IdemID],[@ItemID],tPriceHistory[DateFrom],">=" & [@MyDate],tPriceHistory[DateTo],"<" & [@MyDate])
(I used table formula syntax here - it is easier to write formulas and to get the meaning of them. @ indicates, that the value is read from same row of table, the formula belongs to)