Last price paid for a product, prior to a certain date - Formula

[jwb1012]

Hello, I have a table with historical purchase history (i.e. prices paid for various products over time). Then I have another table, the "product request table", where I need to use a formula to pull in the most recent price paid for a product as of a certain date (not including purchases made after that date). The purchasing data will have purchases +/- the dates in the "product request table".

I cant think of a great way to accomplish this. Any thoughts?

[TABLE="class: grid, width: 495"]
<tbody>[TR]
[TD]Product Requested
[/TD]
[TD]Date Requested
[/TD]
[TD]Last Price Paid
[/TD]
[/TR]
[TR]
[TD]Apple
[/TD]
[TD]10/10/2016
[/TD]
[TD]<insert Formula=""></insert>
[/TD]
[/TR]
[TR]
[TD]Peach
[/TD]
[TD]10/10/2016
[/TD]
[TD]<insert Formula=""></insert>
[/TD]
[/TR]
[TR]
[TD]---------------------
[/TD]
[TD]-----------------
[/TD]
[TD]-------------------
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Product Purchased
[/TD]
[TD]Date Purchased
[/TD]
[TD]Price Paid
[/TD]
[/TR]
[TR]
[TD]Apple
[/TD]
[TD]7/10/2016
[/TD]
[TD]$1.00
[/TD]
[/TR]
[TR]
[TD]Apple
[/TD]
[TD]8/10/2016
[/TD]
[TD]$1.10
[/TD]
[/TR]
[TR]
[TD]Apple
[/TD]
[TD]9/10/2016
[/TD]
[TD]$1.21
[/TD]
[/TR]
[TR]
[TD]Apple
[/TD]
[TD]11/10/2016
[/TD]
[TD]$1.33
[/TD]
[/TR]
[TR]
[TD]Peach
[/TD]
[TD]7/10/2016
[/TD]
[TD]$2.00
[/TD]
[/TR]
[TR]
[TD]Peach
[/TD]
[TD]8/10/2016
[/TD]
[TD]$2.20
[/TD]
[/TR]
[TR]
[TD]Peach
[/TD]
[TD]9/10/2016
[/TD]
[TD]$2.42
[/TD]
[/TR]
[TR]
[TD]Peach
[/TD]
[TD]11/10/2016
[/TD]
[TD]$2.66
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Tetra201]

Try the following formula (to be entered using Ctrl+Shift+Enter, not just Enter):

=LOOKUP($B2,IF($A$8:$A$15=$A2,$B$8:$B$15),IF($A$8:$A$15=$A2,$C$8:$C$15))

 

[jwb1012]

Very interesting... it works... can you please explain how/what this formula is doing in simple terms? Not sure I understand.

 

[Tetra201]

In simple terms: it looks for the latest Apple-related date that does not exceed the 'Date Requested', and returns the corresponding value from the Apple-related array of prices.