SOLVED

How can I get my cell to automatically change based on referenced date data?

Occasional Contributor

Good morning all!

 

I'm trying to get my date(s) to have correlated values and then use a reference cell to see what the assigned value should be. Please see information below as my example:

 

keltzjd_0-1657640766542.png

I want to use the values in the columns "Original Start", "Original Completion", "Updated Start", and "Updated Completion" as the inputs for the formula, but checking against this table:

 

keltzjd_1-1657640850766.png

 

So if the dates in their respective columns fall within any of the ranges in the table above, it assigns the cell the value within the table. 

 

Anyone know how to accomplish this? Thank you!

2 Replies
best response confirmed by keltzjd (Occasional Contributor)
Solution

@keltzjd 

=IFERROR(INDEX($J$2:$J$91,MATCH(1,(A2>=$K$2:$K$91)*(A2<=$L$2:$L$91),0)),"")

Maybe with this formula in cell B2. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.

dates and values.JPG

=IFERROR(INDEX($J$2:$J$91,MATCH(1,(C2>=$K$2:$K$91)*(C2<=$L$2:$L$91),0)),"")

This is the formula in cell D2 in the example.

Worked perfectly, thank you so much!