Búsqueda múltiple de palabras en una celda

Meses atrás publiqué una entrada (ver) donde localizabamos dentro de una celda la coincidencia de palabras de otro listado.

En el post de hoy realizaremos una proceso similar, pero para búsquedas múltiples.

Tenemos un rango A1:A7 de celdas con listado de colores en cada celda, y por otro lado una tabla de colores buscados en cada una de esas celdas (tabla llamada 'TblColores'):

haz clic en la imgen

El proceso es algo laborioso... así pues vamos por partes.

En la TblColores en campo 'equiv' es calculado con la fórmula:
=POTENCIA(2;FILA([@equiv])-FILA(TblColores[[#Encabezados];[equiv]])-1)
que devuelve para cada fila de la tabla el valor devuelto por la potencia de base 2 y exponente 0, 1, 2, 3, 4, etc...
Es decir, obtenemos automáticamente los valores 1, 2, 4, 8, 16, 32, 64, 128, 256 , etc.
Estas cantidades son la base del sistema binario, necesario para la ubicación de colores buscados y encontrados, ya que la suma combinada de estos números es única.

Segundo paso. En el rango B1:B7 añadimos la fórmula:
=SUMAPRODUCTO(NO(ESERR(ENCONTRAR(TblColores[busqueda];$A1)))*TblColores[equiv])
que trabaja matricialmente localizando, si es el caso, cada color existente en la tabla 'TblColores'.
La primera parte de la fórmula
NO(ESERR(ENCONTRAR(TblColores[busqueda];$A1)))
retorna una matriz de VERDDADEROS y FALSOS según existan o no los colores buscados en la celda.
La segunda parte de la fórmula
TblColores[equiv]
se emplea para multiplicar los importes 1, 2, 4, 8, 16, 32, 64, 128, 256 (valores binarios) por esos V y F y así poder sumarlos para obtener un valor único, valor único que representa un sola combinación de dichos valores binarios.

¿Cómo sabemos cuáles son esos valores binarios?... Para descubrirlo aplicaremos la función DEC.A.BIN; y en concreto la siguiente fórmula que completa con ceros por la izquierda ese valor binario devuelto hasta completar el número de dígitos igual a los colores buscados.
En el rango C1:C7:
=SI(LARGO(DEC.A.BIN(B1))<CONTARA(TblColores[busqueda]);REPETIR(0;CONTARA(TblColores[busqueda])-LARGO(DEC.A.BIN(B1)))&DEC.A.BIN(B1);DEC.A.BIN(B1))

En el rango contiguo D1:D7 vemos el resultado simple de DEC.A.BIN(B1).

Último paso. Seleccionamos el rango E1:I1 (son cinco celdas por que como máximo quiero buscar hasta cinco colores por celda).
Y en la celda activa E1 escribo la siguiente fórmula matricial:
=SI.ERROR(TRANSPONER(INDICE(TblColores[busqueda];1+CONTARA(TblColores[busqueda])-FILA(INDIRECTO("1:"&CONTARA(TblColores[busqueda])))*EXTRAE(C1;FILA(INDIRECTO("1:"&CONTARA(TblColores[busqueda])));1);1));"")

La clave de la fórmula es el uso matricial de
EXTRAE(C1;FILA(INDIRECTO("1:"&CONTARA(TblColores[busqueda])));1)
que devuelve el número binario descompuesto en elementos individuales.
Por ejemplo para la primera cadena de texto en la celda A1, EXTRAE retorna: {"1";"1";"1"}
Esta matriz multiplicada por unos números desde 1 hasta el número de colores buscados, lo que obtenemos con:
FILA(INDIRECTO("1:"&CONTARA(TblColores[busqueda])))
Y a dicho producto resultante lo invertimos restándolo del total de colores buscados, ajustado con un +1

Asi para nuestro primer ejemplo obtendríamos la matriz:
{3;2;1}
que empleando la función INDICE nos permite recuperar los colores que responden a esas tres posiciones de la tabla de colores...

Con lo que llegamos al final deseado... por cada celda de A1:A7 tenemos listado horizontalmente los colores coincidentes buscados.