0

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

Improve this question
2
  • 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.
    – doubleunary
    Feb 19 at 7:48
  • Please add your manually entered desired results in the sample spreadsheet.
    – doubleunary
    Feb 20 at 18:58

2 Answers 2

Reset to default
0

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:

=iferror( 
  single( 
    filter( 
      'Active Inventory'!A$2:A, 
      'Active Inventory'!A$2:A 
      > 
      filter(
        '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.

Improve this answer
1
  • Wow, thank you, That worked!!!
    – Chris Dreher
    Feb 21 at 0:42
1

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,)
Improve this answer
5
  • 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.
    – Chris Dreher
    Feb 19 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
    Feb 19 at 19:39
  • I have added a sample document. I cannot give access to the original because it is company property.
    – Chris Dreher
    Feb 20 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
    – Chris Dreher
    Feb 20 at 17:59
  • Even when that next shift may be many rows down
    – Chris Dreher
    Feb 20 at 18:04

Your Answer

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

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