# INDEX/MATCH Formula Help

Options
edited 12/09/19

Hello!

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:

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!

Thanks!

Grant

Tags:

• ✭✭✭✭✭✭
Options

Take your initial MAX formula. Use that in an IF statement to say that if it is a date, use it, otherwise use the MIN of date beyond 7 days.

.

So to get the MAX you have:

=MAX(COLLECT([-]7:[-]25, @cell < [1]7))

To get the MIN of dates beyond next week, just flip your @cell < [1]7 to @cell >= [1]7 and change your function from MAX to MIN.

MIN(COLLECT([-]7:[-]25, @cell >= [1]7))

.

Now we can use both of these to populate an IF statement.

=IF(ISDATE(MAX(COLLECT(..........))), MAX(COLLECT(..........)), MIN(COLLECT(..........)))

• Options

Paul,

In that situation, the max value "< [1]7" would still be the 10/24 date, and still be returned. I think I somehow need to indicate that if its not within 5 days of that specified date, to show the min value.

I'm getting a "#UNPARSABLE" when I use the formula you listed above.

Thanks!

Grant

• ✭✭✭✭✭✭
Options

=IF(COUNTIF([-]7:[-]25, AND(@cell < [1]7 + 7, @cell >= [1]7)) > 0, MAX(COLLECT([-]7:[-]25, [-]7:[-]25, AND(@cell < [1]7 + 7, @cell >= [1]7))), MIN(COLLECT([-]7:[-]25, [-]7:[-]25, @cell >= [1]7)))

I believe that should work.

• ✭✭✭✭✭✭
Options

Can you post the exact formula that gave you the unpareseable error?

• ✭✭✭✭✭✭
Options

=IF(COUNTIF([-]7:[-]25, AND(@cell < [1]7 + 7, @cell >= [1]7)) > 0, MAX(COLLECT([-]7:[-]25, [-]7:[-]25, @cell < [1]7 + 7)), MIN(COLLECT([-]7:[-]25, [-]7:[-]25, @cell >= [1]7)))

The AND in the max statement wasn't doing anything I realized.

• Options

I've tweaked this one a little bit, but think its on the right track. This is the formula I have now, and I'm not having luck with it..

"=IF(COUNTIF([-]7:[-]25, AND(@cell >= \$[1]\$6, @cell < \$[1]\$7)) = 0, MIN(COLLECT([-]7:[-]25, [-]7:[-]25, @cell >= [1]7))), MAX(COLLECT([-]7:[-]25, [-]7:[-]25, @cell < [1]7))"

I'm basically rearranging to say that if there are no dates next week, to show the minimum value beyond next week, but if there are some, to show the highest value in the upcoming week. Any idea why this one would be coming back #UNPARSABLE"?

Thanks!

Grant

• ✭✭✭✭✭✭
Options

You have a misplaced closing parenthesis.

Try this version

=IF(COUNTIF([-]7:[-]25, AND(@cell >= \$[1]\$6, @cell < \$[1]\$7)) = 0, MIN(COLLECT([-]7:[-]25, [-]7:[-]25, @cell >= [1]7)), MAX(COLLECT([-]7:[-]25, [-]7:[-]25, @cell < [1]7)))

• Options

Hi Paul,

I actually went with your formula again, and it worked out, so I must have mistyped something the first time. However, it is giving me the old date like I thought. I changed some of the dates in my range to better show you what I'd like to do.

Basically, since there are no values between 10/28-11/04, I would like to return the 11/5 date in my range. However, the formula written will still return 10/20. The dates will not be deleted after they are past, which is what I think breaks your formula. This is going to be a master list of all dates, past, current, and future.

Thanks for the help!

Grant

• Options

Perfect! Thats what I needed! Thank you!!!

• ✭✭✭✭✭✭
Options

Ah. Ok. I must have misread your original post when I saw that the list of dates was ever changing.

• ✭✭✭✭✭✭
Options

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!