# Google Sheets Formula Help needed - Vlookup/Index/Match

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?

• 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

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.

• 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 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