SOLVED

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

Occasional Contributor

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

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 Replies
best response confirmed by keltzjd (Occasional Contributor)
Solution

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

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

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

Worked perfectly, thank you so much!