# Should I use INDEX/MATCH Instead?

✭✭✭✭✭✭

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. 😎

Tags:

• ✭✭✭✭✭✭

Insert a date type column in your reference sheet and use this formula...

=IF(COUNT(ANCESTORS([Build Number]@row)) = 0, [PROD Deployment]@row)

Then in the target sheet your formula would look something like this...

=MIN(COLLECT({Reference Sheet New Date Column}, {Reference Sheet New Date Column}, @cell >= [Testing End Date]@row))

• ✭✭✭✭✭✭

Are you able to provide screenshots for reference? It sounds like we may need a different formula to accommodate the structure of your reference sheet, but I would need to see it to be sure.

• ✭✭✭✭✭✭

Here is a screen shot of my reference sheet

The IF/AND statement is referencing the PROD dates in Orange (parent). I created a helper column to only show the parent dates thinking that might be the cause.

Frank Smith, PMP

Assistant Director | IT Special Projects Mgr.

Oregon Parks & Recreation Department

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

• ✭✭✭✭✭✭

Can you give an example of what would be expected such as...

If the date is 5 Oct 2021 it should be _________ because it falls within _________ range on the reference sheet.

• ✭✭✭✭✭✭

The date range would be as follows:

Today through 10/26 would be 10/27

10/28 thru 12/7 would be 12/8

12/9 thru 1/18 would be 1/19

So one day after Major PROD date to One day before Major PROD date.

I hope that helps and appreciate the help.

Frank Smith, PMP

Assistant Director | IT Special Projects Mgr.

Oregon Parks & Recreation Department

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

• ✭✭✭✭✭✭

Insert a date type column in your reference sheet and use this formula...

=IF(COUNT(ANCESTORS([Build Number]@row)) = 0, [PROD Deployment]@row)

Then in the target sheet your formula would look something like this...

=MIN(COLLECT({Reference Sheet New Date Column}, {Reference Sheet New Date Column}, @cell >= [Testing End Date]@row))

• ✭✭✭✭✭✭

That worked perfectly!!! It is so much shorter that the multiple IF/AND I was using.

Have a great weekend!

Frank Smith, PMP

Assistant Director | IT Special Projects Mgr.

Oregon Parks & Recreation Department

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

• ✭✭✭✭✭✭

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!