COLLECT function with criteria 'is not specific text'

Fixfire

I am building a COLLECT function to identify the first item in a list that is not coloured green. Colours are represented as text in a column.

My function looks like this but is showing as unparseable. How can I fix it?

=INDEX(COLLECT(Colour:Colour, "!=""Green""", 0,), 1)

I know it's possible using a helper column but this application requires a minimal number of columns so I am avoiding using helper columns unless 100% necessary.

Rich Coles

Hi @Fixfire - something like this?

Formula A =INDEX(COLLECT(Colour:Colour, Colour:Colour, <>"Green"), 1)

Formula B =INDEX(COLLECT(Reference:Reference, Colour:Colour, <>"Green"), 1)

Hope that helps!