Returning value based on date in another column

Sam Swain

Looking for a solution / formula type on the following:

• I want the Actual Phase Number column to give a number between 1 and 8.
• The number it will give is calculated by first looking at the Actual DOC column. The date in row 1 showing 12/13/22 will look at Scheduled DOC and find the most recent date before. In this case, the most recent date before 12/13/22 is 12/02/22.
• The Phase Number column for 12/02/22 shows '7'
• I therefore want row 1 of the Actual Phase Number row to return '7'

Paul Newcome

You would need to use something like this:

=INDEX([Phase Number]:[Phase Number], MATCH(MAX(COLLECT([Scheduled DOC]:[Scheduled DOC], [Scheduled DOC]:[Scheduled DOC], @cell<= [Actual DOC]@row)), [Scheduled DOC]:[Scheduled DOC], 0))

Paul Newcome

You would need to use something like this:

=INDEX([Phase Number]:[Phase Number], MATCH(MAX(COLLECT([Scheduled DOC]:[Scheduled DOC], [Scheduled DOC]:[Scheduled DOC], @cell<= [Actual DOC]@row)), [Scheduled DOC]:[Scheduled DOC], 0))

Sam Swain

That worked thanks Paul!!!

Paul Newcome

Happy to help. 👍️