Return the most recent unit price of a matching item that is dated on or before a specified lookup date

Question

In Google Sheets I have a list of orders recorded by order date, item, and price.

There is one order per row, but the same items are ordered on different dates and the prices vary between orders.

I'm trying to return the most recent order price for a lookup item restricted to only orders made on or before a specified date.

In the sample data below, my lookup values (batch date and item) are in the sheet "Batching log" while the lookup range is in "Orders".

Batching log (sheet 1)

batch date	item	unit price
1-feb-2024	sand	formula

Orders (sheet 2)

order date	Item	unit price
05-feb-2024	sand	$2.50
20-jan-2024	sand	$2.00
21-apr-2024	stone	$32.00
27-oct-2023	timber	$13.00
12-nov-2023	sand	$5.00
28-jan-2024	timber	$12.00
13-mar-2024	stone	$35.00

Based on the data above, the formula in 'Batching log'!C2 should return a unit price of $2.00

This is because, of the three separate orders whose item is "sand" (rows 2, 3,and 6), row 2's date 20-jan-2024 is the most recent without exceeding the lookup date.

There are some similar questions on the site but they're different enough that I can't quite work it out.

Answer 1

VLOOKUP

=VLOOKUP(A2, SORT(FILTER(Orders!A:C, Orders!B:B=B2)), 3, 1)

FILTER  SORT  VLOOKUP

1. FILTER Orders sheet for rows where item is "sand" and SORT by date.
2. Use VLOOKUP to match the date using is_sorted=1 which returns the closest date that is equal to or less, and return the value in column index 3.

INDEX and SORT

=INDEX(
   SORT(
     Orders!C2:C,   
     Orders!B2:B=B2,0, 
     Orders!A2:A<=A2,0, 
     Orders!A2:A,0), 1)

INDEX  SORT

1. The unit price column is sorted by the following columns:
   1. A column of 1's and 0's from testing each row in Orders for Item="sand"
   2. Another similar column from testing if order date<=batch date
   3. The last column is simply the order date
2. INDEX returns the first row from the array of unit prices returned by SORT

SORTN

As pointed out by @the-god-of-biscuits, the combination of INDEX and SORT can be replaced by a single function, SORTN.

=SORTN(
   Orders!C2:C, 1, 0,   
   Orders!B2:B=B2,0, 
   Orders!A2:A<=A2,0, 
   Orders!A2:A,0)

SORTN

1. SORTN has the number of rows to return set to 1 and duplicate handling is set to 0 (no duplicates).
2. The rest of the formula mirrors the previous one except INDEX is no longer needed.

QUERY

=QUERY(
   Orders!A2:C, "SELECT C WHERE B = '"& B2 &"' 
   AND A <= date '"& TEXT(E2,"yyyy-mm-dd") &"'
   ORDER BY A DESC LIMIT 1") 

QUERY  TEXT

1. Queries the range Orders!A2:C where:
   1. Order item=Batch item
   2. order date<=Batch date
2. Sorts the results by order date descending.
3. Limits the results to 1 row, which is the most recent Batch price whose date and item match the conditions.