Fill Column based on Specific data from another Column

Jack1295 

This formula utilizes a recursive Lambda called 'ExtractColor'.  I thought it was best to avoid functions that would be slow (e.g. SEARCH, FIND) to calculate with a large data set and go with a function that will terminate when a color is found.

A few presumptions:

-each cell has 1 color

-you provide a list of colors to locate

=BYROW(texts, LAMBDA(each_text, ExtractColor(each_text, ColorsTbl)))

ExtractColor being:

ExtractColor = LAMBDA(string, list_of_colors,
        LET(
            next_color, TAKE(list_of_colors, 1),
            err, ISERROR(next_color),
            check, ISNUMBER(
                XMATCH("*" & next_color & "*", string, 2)
            ),
            discard, DROP(list_of_colors, 1),
            IF(
                check,
                next_color,
                IF(err, "", ExtractColor(string, discard))
            )
        )
    )

The above function resides in the attached workbook. An easy way to use it is to simply lay in your data in the workbook (move/copy sheet into existing workbook).

This gets a little easier once the Regex functions become available!