Encontrar palabras de una lista en una celda

En el post de hoy trataremos un caso frecuente: cómo localizar elementos de una lista dentro de una celda.

Todo nace a partir de la duda planteada por un usuario:

[...]Tengo que trabajar sobre una base de datos extensa, y en una columna de DESCRIPCIÓN tengo información que no siempre viene escrita de la misma manera. Lo que necesito es identificar una palabra dentro de un texto, y ser capaz de extraer esa palabra, para luego poder hacer categorías mayores. Lo que necesito es que si aparece en cualquier parte del texto la palabra Azul, la extraiga a la columna GRUPO. [...]

Inicialmente pensé que no se podría realizar con funciones estándar, y que habría que pasar por una función personalizada... pero después de algunas pruebas conseguí el objetivo mediante el empleo de funciones de Excel: INDICE, ESNUMERO, SUMAPRODUCTO, HALLAR o ENCONTRAR y FILA.

Veamos el planteamiento de partida:

La idea a lograr es que en cada una de las celdas de la columna de DESCRIPCIÓN localizar una de las palabras de la 'lista de colores'.

Comenzaremos, para facilitar nuestro trabajo y darle algo de dinamismo, asignándole un nombre definido al campo de la tabla:
Lista =Tabla2[lista colores]

La función que incorporamos a nuestro rango, en la celda B2 es:
=INDICE(Lista;SUMAPRODUCTO(ESNUMERO(HALLAR(Lista;A2))*(FILA(Lista)-1)))

Analizamos la función por partes.
En lo más 'profundo' encontramos
HALLAR(Lista;A2)
(si necesitamos precisión en cuanto a mayúsculas y minúsculas emplearemos la función ENCONTRAR).
Esta función bajo un entorno matricial devuelve un conjunto de números y errores para cada una de las palabras/colores buscados. Por ejemplo en la celda B2 de nuestro ejemplo obtendríamos:
{8;#¡VALOR!;#¡VALOR!}
Al anidarlo dentro de la función ESNUMERO convertimos esa matriz de datos en una matriz lógica de VERDADEROS y FALSOS:
{VERDADERO;FALSO;FALSO}

Por otro lado tenemos una segunda matriz de constantes que corresponderán a las filas en las que se encuentran los colores buscados... llegamos a estos números con lo siguiente:
(FILA(Lista)-1)
que devuelve:
{1;2;3}
Notemos el ajuste de -1 para salvar la posición del encabezado de la Tabla.

La función SUMAPRODUCTO hace el resto, multiplicando ambas matrices elemento a elemento y sumando el resultado de cada multiplicación individual.. retornando un solo valor:
{VERDADERO;FALSO;FALSO} * {1;2;3}
=1+0+0 =1

Ese número retornado, equivale al número de fila del dato buscado dentro del campo de nuestra tabla de colores, se emplea de manera directa con la función de búsqueda INDICE.
Con lo que finalmente conseguimos nuestro objetivo, recuperamos el color que existe en la celda de la 'descripción':

Obviamente esto solo sirve para localizar una sola palabra...