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

[keltzjd]

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:

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:

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 · Accepted 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.

=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.

[#3]

Worked perfectly, thank you so much!