LOOKUP price based on date (but not necessarily the most recent date)

[Yamezz]

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:

=VLOOKUP(L10718,InputCosts,2,FALSE)

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.

 

[Peter_SSs]

, I did change create the table by selecting cells and using the Insert Table command, then I changed the table name to InputCosts.

That is not what your picture is showing.

Delete the Cost, Date and Item names from the Name Manager, then enter the formula I posted previously.

Be sure that the Item name that you are trying to look up from column L has exactly the same spelling as that item in the table. eg check for any trailing or leading spaces. If the formula cannot find any matching item names it would return a #NUM! error.

 

[Yamezz]

The picture I posted was after I started again with a blank file and did not name the table. I was trying to get to the conditions you reference in the formula.

Either way, I've managed to get it going. It required me to put both the table and the formula on the one sheet so I could see where the cell references were not correct. When I dragged the references into place it worked, but errored out when I changed the test date. That was when I realised some of the dates got messed up between AU and US format. All is working now. Thank you Peter.

 

[Peter_SSs]

Glad you got it resolved. Thanks for letting us know.