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 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.
    – ztiaa
    Mar 3 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 at 12:45
  • I edited the formula. Please see if that helps.
    – ztiaa
    Mar 3 at 14:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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