Pull prices through to a sheet using a date range to keep historical data

Linziw

I have 2 sheets, a Price Register sheet and a Delivery sheet. I want to pull the prices per tonne from the price register sheet into the delivery sheet if the Supplier and description match, and the price is current. I have rows to show old prices between, say 01/01/2026 and 10/03/2026, then a new price starts on 11/03/2026 and is ongoing. I have tried several formulas but nothing will work with a date range. the prices will pull through without the dates but will look at the latest price and use that for everything. Any help would be appreciated! I feel like I am going around in circles! Example formula: =IFERROR(
INDEX(
COLLECT(
{05. SEL Feedstock Price Register Price},
{05. SEL Feedstock Price Register Feedstock Descrip}, [Feedstock Description]@row,
{05. SEL Feedstock Price Register Supplier}, [Feedstock Supplier]@row,
{05. SEL Feedstock Price Register Date From}, "<=" + [Date]@row,
{05. SEL Feedstock Price Register Date To}, ">=" + [Date]@row
),
1
),
IFERROR(
INDEX(
COLLECT(
{05. SEL Feedstock Price Register Price},
{05. SEL Feedstock Price Register Feedstock Descrip}, [Feedstock Description]@row,
{05. SEL Feedstock Price Register Supplier}, [Feedstock Supplier]@row,
{05. SEL Feedstock Price Register Date From}, "<=" + [Date]@row,
{05. SEL Feedstock Price Register Date To}, ""
),
1
),
"NO MATCH"
))

Heidi Truitt

If you are using the "Index(Collect() the first matching row will return NOT the most current valid row.If you have multiple rows for the same Supplier + Description over time, you need to return the row with the latest [Date From] that is still valid for the delivery date. Smartsheet supports date comparisons in criteria, but "Collect" does not sort the matches for you.

I would add a Max valid [Date]