Should I use INDEX/MATCH Instead?

Frank S.
Frank S. ✭✭✭✭✭✭

On sheet A I have a date, if it falls within 2 dates I want to display a PROD date from another sheet.

I can get IF/AND working

=IF(AND([Testing End Date]@row > DATE(2021, 10, 1), [Testing End Date]@row <= DATE(2021, 10, 27)), {Build Release Plan Range 1}, IF(AND([Testing End Date]@row > DATE(2021, 10, 28), [Testing End Date]@row <= DATE(2021, 12, 8)), {Build Release Plan Range 2}, IF(AND([Testing End Date]@row >= DATE(2021, 12, 9), [Testing End Date]@row <= DATE(2022, 1, 19)), {Build Release Plan Range 3}, " ")))

I have a total of 14 release dates and think the IF/AND will get too long.

I'm trying to get INDEX/MATCH to work, any suggestions?

My progress so far:

=INDEX({Build Release Plan Range 6}, MATCH([Testing End Date]@row, {Build Release Plan Range 6}, 1))

This supplies the wrong PROD date (i.e 12/6/21 should be 12/8/21 but shows previous PROD release of 10/27/21. 0 returns no match and -1 returns blank.

Any suggestions?

Frank Smith, PMP

Assistant Director | IT Special Projects Mgr.

Oregon Parks & Recreation Department

If my response helps, please mark it as an accepted answer. 😎


Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!