# Query/Filter function finding the closest date that is less than the order date for an array of dates using a given variable

## 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 `Date`s 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.

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

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)`