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 sheetItems
by comparing theDate
s in theItems
sheet with the selectedOrder Date
from
theOrders
sheet. Making sure to select thePrice
which is less than but
closest to theOrder Date
inLabels
for the givenID1
.
Problem and Formula
I can get the price for the given
ID1
; however, if two or moreID1
'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
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 sheetItems
.This is the corrected formula for cell
C2
in sheetProducts
: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