Need better solution for multiple nested IF statement

K Morano

Hello,

I have a sheet that tracks sprint numbers based on their date ranges. How can I identify the sprint number for a task using the task start date? Right now I'm using this very long formula in the Return sprint number column, and I'm hoping there is a better way to do this. Thank you!

=IF(AND([Task Start Date]@row >= [Start Sprint]1, [Task Start Date]@row <= [End Sprint]1), [Sprint Number]1, IF(AND([Task Start Date]@row >= [Start Sprint]2, [Task Start Date]@row <= [End Sprint]2), [Sprint Number]2, IF(AND([Task Start Date]@row >= [Start Sprint]3, [Task Start Date]@row <= [End Sprint]3), [Sprint Number]3, IF(AND([Task Start Date]@row >= [Start Sprint]4, [Task Start Date]@row <= [End Sprint]4), [Sprint Number]4, IF(AND([Task Start Date]@row >= [Start Sprint]5, [Task Start Date]@row <= [End Sprint]5), [Sprint Number]5, IF(AND([Task Start Date]@row >= [Start Sprint]6, [Task Start Date]@row <= [End Sprint]6), [Sprint Number]6, IF(AND([Task Start Date]@row >= [Start Sprint]7, [Task Start Date]@row <= [End Sprint]7), [Sprint Number]7, IF(AND([Task Start Date]@row >= [Start Sprint]8, [Task Start Date]@row <= [End Sprint]8), [Sprint Number]8, IF(AND([Task Start Date]@row >= [Start Sprint]9, [Task Start Date]@row <= [End Sprint]9), [Sprint Number]9, IF(AND([Task Start Date]@row >= [Start Sprint]10, [Task Start Date]@row <= [End Sprint]10), [Sprint Number]10))))))))))

sprint number sheet.png

DKazatsky2

Hi @K Morano,

Give this a try.

=INDEX(COLLECT([Sprint Number]:[Sprint Number], [Start Sprint]:[Start Sprint], @cell <= [Task Start Date]@row, [End Sprint]:[End Sprint], @cell >= [Task Start Date]@row), 1)

Hope this helps,

Dave

K Morano

Hi @DKazatsky2. Thank you so much! That worked. Can I complicate the matter and ask how to write the formula if I am looking up the sprint number from a another sheet? For example, I have a sheet called Project Sheet with the columns Task Start Date and Sprint Number and I need to reference the sheet Sprint Dates (with the sprint number, start sprint, end sprint as in the attachment above). Are you able to show me what that formula looks like? Thanks again! In any case, you've been so helpful and at least got me started and showed me the Index Collect formula.

K Morano

@DKazatsky2 Here is the formula I created attempting to reference another sheet. The sheet with the sprint number, start sprint, and end sprint columns is called Oracle Sprint Dates. The other sheet where I am attempting to capture the sprint number is called Reports Project and has the columns Task Start Date and Sprint Number. I created the formula in the Sprint Number column on the Reports Project sheet. Here is the formula I created, but it doesn't work. Any thoughts?

=INDEX(COLLECT({Oracle Sprint Dates Sprint Number}:{Oracle Sprint Dates Sprint Number}, {Oracle Sprint Dates Start Sprint}:{Oracle Sprint Dates Start Sprint}, @cell <=[Task Start Date]@row, {Oracle Sprint Dates End Sprint}:{Oracle Sprint Dates End Sprint}, @cell>=[Task Start Date]@row), 1)

K Morano

One more comment - I figured out how to write the formula referencing another sheet, in case others are interested:

=INDEX(COLLECT({Oracle Sprint Dates Sprint Number}, {Oracle Sprint Dates Start Sprint}, @cell <= [Task Start Date]@row, {Oracle Sprint Dates End Sprint}, @cell >= [Task Start Date]@row), 1)