0

I have one sheet with the following information:

enter image description here

In another sheet I have the following:

enter image description here

How can I fill in the cells in column B in my first sheet, based on the date? The date should be between the start- and end date.

1 Answer 1

1

In Sheet1 B2 enter:

=index(ifna(vlookup(A2:A,{Sheet2!B2:B,Sheet2!A2:A},2)))

Update:

={"Sprint";
flatten(index(iferror(--query(if(
(transpose(indirect("A2:A"&max(row(A:A)*(A:A<>""))))>=Sprints!B2:B)*
(transpose(indirect("A2:A"&max(row(A:A)*(A:A<>""))))<=Sprints!C2:C),
Sprints!A2:A,),,9^9))))}
4
  • How does that take the end date into account?
    – nielsv
    Mar 3, 2022 at 9:39
  • vlookup() with last parameter omitted searches for the greatest value less than or equal to the search key. You don't mention, however, what should happen if the date logged is between the end date and the start date. The solution I provided ignores the end date column and assumes that the next start date is the previous end date. Does that make sense? If that's not the desired result please share the sheet with edit rights and explain exactly what's the result you want.
    – user290033
    Mar 3, 2022 at 9:51
  • you can find the sheet here (docs.google.com/spreadsheets/d/…). In the first tab, the sprint for the last row should be empty, because it's not defined in the second tab.
    – nielsv
    Mar 3, 2022 at 12:45
  • I edited the formula. Please see if that helps.
    – user290033
    Mar 3, 2022 at 14:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.