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.

  • Nice one! I made edits to your post but was really impressed by quality of your question, especially, including the markdown tables. If you are not comfortable with any of the edits, feel free to make changes or even roll the entire edit back. 👍
    – Blindspots
    Commented Apr 30 at 13:53

1 Answer 1



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


  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.


     Orders!A2:A,0), 1)


  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


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

   Orders!C2:C, 1, 0,   


  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.


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


  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.
  • Also doable with SORTN: =sortn(Orders!C2:C,1,0,Orders!B2:B=B2,0,Orders!A2:A<=A2,0) Commented Apr 30 at 15:55
  • Not sure why you think SORTN isn't an array formula - it accepts scalar operators/functions within the sort_column arguments and iterates them over the associated range/array without needing an ARRAYFORMULA/INDEX wrapper in exactly the same manner as SORT & FILTER, and of course will return >1 result if the n argument >1... Commented Apr 30 at 21:32
  • 1
    No problem! - you weren't being a jerk. Good spot re. the missing date column in my answer - my answer worked on the example data but wouldn't have worked more generally. Commented May 1 at 8:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.