Date comparison not working

[Ronderbecke]

I am trying to compare a date in a cell to get closest without going over so that it pulls the correct data but its not working? I know I'm doing something wrong but cannot figure out how to fix it. Here is the formula I am using:
=index(query(B3:C4,"select B where C>=DATE'"&TEXT(C3,"yyyy-MM-dd")))
The data looks like this:

Dates of change	Date of sale
8/1/2021	8/9/2021
9/17/2021	9/30/2021
12/13/2021
1/17/2022

I want to compare the dates so that the 8/9/2021 shows 8/1/2021 and the 9/30/2021 shows the 9/17/2021 (so that its always the previous or equal to the date of change. Am I even using the right formula for this?

 

[duggie33]

Hi Ronderbecke,

Is this an Excel question? I am not aware of a "query" function that uses a "select" statement.

Doug

 

[Ronderbecke]

Sorry was doing it in google sheets to try to get a function that would work. Can't make it work in excel or in google sheets.

 

[Ronderbecke]

I thought I figured it out using
=FILTER(A1:A10,ABS(B2-A1:A10)=min(ABS(B2-A1:A10)))
but that only works if its closer to the 8/1 date. so if it goes to 9/16 it chooses 9/17 rather than 8/1 and I need it to choose 8/1, is there a way to adjust this and make it work?

 

[duggie33]

Try this:

=INDEX($A$2:$A$10,MATCH(B3,$A$2:$A$10,1))

You will have to adjust the ranges to fit your data. The match uses "less than" for the last argument.

I hope that helps,

Doug

 

[Ronderbecke]

Try this:

=INDEX($A$2:$A$10,MATCH(B3,$A$2:$A$10,1))

You will have to adjust the ranges to fit your data. The match uses "less than" for the last argument.

I hope that helps,

Doug

Using that equation sends this error:
ErrorDid not find value '44454' in MATCH evaluation.
I am guessing this is because I need to use an indirect on the B3 but wouldn't I have to use indirects on the A column as well? Or am I incorrectly interpreting the error? Thanks so much for your help here.

 

[duggie33]

Are you using Excel? I don't recall seeing an error like that before.

 

[Ronderbecke]

Are you using Excel? I don't recall seeing an error like that before.

Sorry I did that in the google sheets, I put it in excel and it worked wonderfully. Thanks so much. Any chance you know how to make it work in google sheets as well?

 

[duggie33]

Sorry, I don't. Glad to help.

Doug