I am looking to create an excel formula that will retrieve the "Date Before" and the "Date After" a specific Date from a list. For example:

The date of my report is in cell S13 (January 14th, 2023), and I am looking to pull the date right before it and right after from the choices in column Q. In this example, the date right before is cell Q13 (Jan 5, 2023) and the date right after is cell Q14 (Feb 5, 2023). How can I create an automated formula to pull this? Thank you!

CodePudding user response：

You can try this on cell D2 to obtain both values:

=LET(rng, A2:A20, refDate, C2, 
  TRANSPOSE(SORT(FILTER(rng, (rng<>"") 
  * ((rng = MAXIFS(rng, rng,"<"&refDate)) 
      (rng = MINIFS(rng, rng,">"&refDate)))))))

Here is the result:

Notes:

1. The OR-condition in FILTER function is stablished by addition of two logical conditions: A B in the second input argument and AND-condition by multiplication, i.e. A*B.
2. To transpose an array column you can use also TOROW.
3. The LET function is used to simplify the formula maintenance, so we define rng and refDate any change to those variables you can change only one time in the formula
4. (rng <>"") is added to exclude empty rows

CodePudding user response：

Try

=INDEX(Q10:Q28,MATCH(0,Q10:Q28-$S$13,1))

and

=INDEX(Q10:Q28,MATCH(0,Q10:Q28-$S$13,1) 1)

entered as array formulas (ctrl shift ENTER).