this is my first time on here, I'm hoping someone can help me :)

I have a formula that finds the final occurrence of a value in a range and gives me the date for that occurrence

=ArrayFormula(LOOKUP(2,1/('Active Inventory'!$F$2:$F$400=$G3),'Active Inventory'!$A$2:$A$400))

It works great, the problem is I need to return the next date in the cells below the given one. here is a formula that gives me the next cell (date) after of the first occurrence of my value

=INDEX('Active Inventory'!$A$2:$A$400,MATCH($G8,'Active Inventory'!$F$2:$F$400,0)+1,)

but how do I get the next Date down (even when there are some cells below with the same date) after the last occurrence of my value?

Link to Sample document https://docs.google.com/spreadsheets/d/1yDc1fXIH_S6WxsqFGHi9VUW_d4V5aTeaT7fv5c_xR1c/edit?usp=sharing

  • Welcome to Web Applications Stack Exchange. Please edit your question and insert a table of sample data together with another table that shows your manually entered desired results. Also consider sharing a publicly editable sample spreadsheet. There is a blank sheet maker that lets you share safely. Commented Feb 19, 2023 at 7:48
  • Please add your manually entered desired results in the sample spreadsheet. Commented Feb 20, 2023 at 18:58

2 Answers 2


Use filter() to get the shift of the last occurrence of a container name, then another filter() to get shifts that come after that shift, and finally single() to get the first one, like this:

      'Active Inventory'!A$2:A, 
      'Active Inventory'!A$2:A 
        'Active Inventory'!A$2:A, 
        row('Active Inventory'!A$2:A) 
        xmatch(B2, 'Active Inventory'!D$1:D, 0, -1) 
  "no next shift" 

Format the results the same way you format shifts.

  • Wow, thank you, That worked!!! Commented Feb 21, 2023 at 0:42

You may try with XMATCH from bottom to top search:

=INDEX('Active Inventory'!$A$2:$A$400,XMATCH($G8,'Active Inventory'!$F$2:$F$400,0,-1)+1,)
  • That worked exactly as it should Thank You! - but i just realized I need the next date down even though there are more rows below that have the same date. Commented Feb 19, 2023 at 17:10
  • Could you please add some details about it in the question? Add some table and sample desired output in the original question
    – Martín
    Commented Feb 19, 2023 at 19:39
  • I have added a sample document. I cannot give access to the original because it is company property. Commented Feb 20, 2023 at 17:52
  • I need to return the next Date and shift AM/PM after the date and shift when a chemical container stopped showing up in column "D" Active Inventory Tab Commented Feb 20, 2023 at 17:59
  • Even when that next shift may be many rows down Commented Feb 20, 2023 at 18:04

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.