Google Sheets – Query/Filter Function to Find Closest Date Less Than Order Date

google sheetsgoogle-sheets-arrayformulagoogle-sheets-dates

Overview

I need to get the cost of something by comparing item dates against an order date for one or more of the same item with different item dates, and making sure to select the item date that is less than but closest to the order date.

Specifically, I need to get the Price value from the sheet Items
by comparing the Dates in the Items sheet with the selected Order Date from
the Orders sheet. Making sure to select the Price which is less than but
closest to the Order Date in Labels for the given ID1.


Problem and Formula

I can get the price for the given ID1; however, if two or more ID1's
share the same date than the following formula breaks.

IF($B$2:$B <> "",ARRAYFORMULA(VLOOKUP(MAX(FILTER(FILTER(Items!$C$2:$C,Items!$A$2:$A = B2),FILTER(Orders!$B$2:$B,Orders!$A$2:$A = A2) >= FILTER(Items!$C$2:$C,Items!$A$2:$A =B2))),{Items!$C$2:$C,Items!$B$2:$B},2,0)),"")

The formula here works for the first look up; however, if the same date is used for other items the formula breaks and returns the first one. It's due to how the VLOOKUP function is using the date to look up the entries; however, I don't know how to make it so it uses ID1 as a filter.


How I went about it

Step 1: Get Order Date from ID2

INDEX(Orders!$B$2:$B,MATCH(A2,Orders!$A$2:$A,0)) or FILTER(Orders!$B$2:$B,Orders!$A$2:$A = A2)

Step 2: Get Price from ID1

FILTER(Items!$C$2:$C,Items!$A$2:$A = B2)

Step 3: Compare the Item Date to the Order Date and get the Date that is less than but closest to the Order Date

MAX(FILTER(FILTER(Items!$C$2:$C,Items!$A$2:$A = B2),FILTER(Orders!$B$2:$B,Orders!$A$2:$A = A2) >= FILTER(Items!$C$2:$C,Items!$A$2:$A =B2)))

I believe this is incorrect, but I'm not sure how to have it use ID1 as its filter, or get the closest value instead of returning all values before the order date.

Step 4: Get the selected Price.


Google-sheet links

Read Only | Writable


If I have not made the question clear enough, I'll try my best to explain it further.

Best Answer

There is something missing from your formula.
You did not take into account the ID1 variable in sheet Items.

This is the corrected formula for cell C2 in sheet Products:

=IF($B$2:$B <> "",ARRAYFORMULA(VLOOKUP(MAX(FILTER(FILTER(Items!$C$2:$C,Items!$A$2:$A = B2),FILTER(Orders!$B$2:$B,Orders!$A$2:$A = A2) >= FILTER(Items!$C$2:$C,Items!$A$2:$A =B2))),{Items!$C$2:$C,Items!$B$2:$B}*(Items!$A$2:$A=B2),2,0)),"")

What is needed is the addition of *(Items!$A$2:$A=B2) so your formula last part becomes {Items!$C$2:$C,Items!$B$2:$B}*(Items!$A$2:$A=B2)

Please have look at the corrected column