INDEX/MATCH Formula Help

edited 12/09/19 in Formulas and Functions


I'm creating a document where I would like to index a column of dates, and return a specific date from that list. I need one of two things to occur: 

1) If a date occurs in the upcoming week, I would like to return the latest date of the week

2) If no date occurs in the upcoming week, I would like to return the minimum value of all dates greater than the upcoming Friday. 

I've included a screenshot, and below is a description of what I would like to do: 

I would like Column[Deadline] under current deadline to match whatever deadline in the all deadlines matches the returned date in Column[-]. I have this figured out already. 

My issue is in getting Row 3, Column[-] to return the correct date. My current formula is, "=MAX(COLLECT([-]7:[-]25, @cell < [1]7))". That way, when I have two dates (10/22 & 10/24) in the upcoming week, it will show the maximum value. My issue is that when I am on the week of 10/28, the returned date will be the highest value less than 11/4, which would still be my 10/24 deadline, even though it would have already occurred. I need, in the event that there are no deadlines between 10/28-11/4, to show the minimum date beyond 11/4, in this case, 11/13. 

A heads up that the table within the black border auto updates from week to week. On Tuesday, the current cell saying 10/21 will change to the following Monday, and all cells below will update by one week. 

Looking for help with a formula that can return a max of next week, or minimum or a range beyond next week! 







Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!