I would like a formula to return the price of an item from a list that includes historical pricing.
I have been using the following formula to return the item price:
which works well when there is only one price listed for an item. I now need to update the price going forward with the new price, however I don't want to update the historical records to the new price.
I need to use the date in column A to return the correct price, with logic something along the lines of: Price = most recent price from list, before the date in column A.
For example, if the Item in column L is "Pads" and the value in column A is 7-May, the result should be $3.50, but if the date is 5-May, then the result should be $2.75
My "InputCosts" table is attached. I'd appreciate any help I can get.
I have been using the following formula to return the item price:
Excel Formula:
=VLOOKUP(L10718,InputCosts,2,FALSE)
I need to use the date in column A to return the correct price, with logic something along the lines of: Price = most recent price from list, before the date in column A.
For example, if the Item in column L is "Pads" and the value in column A is 7-May, the result should be $3.50, but if the date is 5-May, then the result should be $2.75
My "InputCosts" table is attached. I'd appreciate any help I can get.