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?

Find more posts tagged with

Formulas

Accepted answers

Paul Newcome

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))

All comments

Paul Newcome

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.

Frank S.

@Paul Newcome

Here is a screen shot of my reference sheet

Screen Shot 2021-10-07 at 7.23.20 AM.png

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.

Paul Newcome

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.

Frank S.

@Paul Newcome

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.

Paul Newcome

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))

Frank S.

@Paul Newcome

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

Have a great weekend!

Paul Newcome

Happy to help. 👍️

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.