Lookup between two dates

[Stefan_Sand]

Hello,

I need help with a formula, i want to get a sprint number, if a given date of a workshop is within a sprint cycle.
i wanted to use sumproduct,but i wont get the proper answer. Maybe i am missing something.
Can you take a short look over my example, wher i am going wrong?

kind regards stefan

[HansV]

You switched <= and >=.

=SUMPRODUCT(($C$3:$C$111<=$G$3)*($D$3:$D$111>=$G$3)*$B$3:$B$111)

In German:

=SUMMENPRODUKT(($C$3:$C$111<=$G$3)*($D$3:$D$111>=$G$3)*$B$3:$B$111)

If you have Microsoft 365, Office 2021 or Office 2024, you can also use

=FILTER(B3:B111, (C3:C111<=G3)*(D3:D111>=G3), "No Match")

In German, use ; instead of ,

If you have an older version:

=IFERROR(INDEX(B3:B111, MATCH(1, (C3:C111<=G3)*(D3:D111>=G3), 0)), "No Match")

In German:

=WENNFEHLER(INDEX(B3:B111; VERGLEICH(1; (C3:C111<=G3)*(D3:D111>=G3); 0)); "No Match")

[snb]

=INDEX(B3:B111;MATCH(G3;C3:C111;1)+1)

[Stefan_Sand]

Ahhhhh, thank you so much, Hans!
& SNB

[snb]

Since there's some regularity in your data:

=B3+INT((G3-C3)/14)+1