How to get VLOOUP to skip an instance if specific condition not met?

Ryan Kinder

I am getting out of my depth and would like some help with a complicated equation.

Background Info: Trying to streamline an item check in/out system that uses barcode SKUs and an item condition as a freeform explanation from the user. An item can be checked out/in by users and when they are checked in it will prompt the user to report the condition of the item. The default value is None for when the item is still pristine, but if there are some issues the user types a few sentences to explain.

Problem: Currently I am using VLOOKUP to find the first instance of a SKU and report back the condition explanation input by the user on a form. Sometimes the item is checked out again after an item explanation has been reported that is other than None. When it is checked back in and there is no change to this explanation I would like for them to be able to type Repeat instead of another couple sentences that re-explain an already reported issue. I am wondering if there is a way to get VLOOKUP to skip instances where the SKU matches up but the item explanation is Repeat, and keep looking until it finds the full explanation?

Any help is greatly appreciated!

Kelly Moore

Hi Ryan

An INDEX(COLLECT) allows you to build criteria into a lookup-type formula. The formula might look something like this (I made guesses on your column names. Note I'm leveraging the system generated Created (Date) column to find the max date that doesn't have 'Repeat'. A system generated Autonumber could also work).

=INDEX(COLLECT(Condition:Condition, Created:Created, MAX(Created:Created)<=Created@row, SKU:SKU,@cell=SKU@row, Condition:Condition, @cell<>"Repeat"),COUNTIFS(Created:Created, MAX(Created:Created)<Created@row, SKU:SKU,@cell=SKU@row, Condition:Condition, @cell<>"Repeat"))

The countifs with same criteria as the Collect function is determining the row_index portion of the INDEX function

This is just one possibility of a solution. I'm sure our esteemed community will offer alternatives.