EXCEL: Obtener la primera celda que contiene contiene

Para verificar una celda de una de varias cosas y devolver la primera coincidencia encontrada en la lista, puede usar una fórmula INDICE / COINCIDIR que use HALLAR o ENCONTRAR para ubicar una coincidencia. En el ejemplo que se muestra, la fórmula en C5 es:

{=INDICE(things,COINCIDIR(VERDADERO,ESNUMERO(HALLAR(things,B5)),0))}

donde "cosas" es el rango nombrado E5: E9.

Nota: esta es una fórmula de matriz y debe ingresarse con Control + Shift + Enter.

Cómo funciona esta fórmula

En este ejemplo, tenemos una lista de colores en un rango con nombre llamado "cosas" (B5: B11). Queremos verificar el texto en la columna B para ver si contiene alguno de estos colores. Si es así, queremos devolver el nombre del primer color encontrado.

Trabajando de adentro hacia afuera, esta fórmula usa la función ESNUMERO y la función HALLAR para buscar el texto en B5 para cada color listado en "cosas" como esta:

ESNUMERO(HALLAR(things,B5)

Esta expresión se basa en una fórmula (explicada en detalle aquí) que verifica una celda para una sola subcadena. Si la celda contiene la subcadena, la expresión devuelve VERDADERO. Si no, la expresión devuelve FALSO.

Cuando le damos a este HALLAR una lista de cosas (en lugar de una cosa) nos devolverá una serie de resultados. Cada color encontrado generará una posición numérica, y los colores no encontrados generarán un error:

{#VALUE!;#VALUE!;20;#VALUE!;#VALUE!}

La función ESNUMERO convierte los resultados a valores VERDADERO / FALSO. Cualquier número se convierte en VERDADERO y cualquier error (no encontrado) se convierte en FALSO. El resultado es una matriz como esta:

{FALSO;FALSO;VERDADERO;FALSO;FALSO}

Esta matriz se devuelve a la función COINCIDIR como argumento de la matriz. El valor de búsqueda es VERDADERO y el tipo de coincidencia se establece en cero para forzar una coincidencia exacta. Cuando hay un color coincidente, COINCIDIR devuelve la posición que VERDADERO encontró por primera vez. Este valor se introduce en la función INDICE como el número de fila, con el rango "cosas" con nombre proporcionado como la matriz. Cuando hay al menos una coincidencia, INDICE devuelve el color en esa posición. Cuando no se encuentra ninguna coincidencia, esta fórmula devuelve el error # N / A.

Con valores codificados

Si no desea configurar un rango con nombre externo como "cosas" en este ejemplo, puede codificar los valores en la fórmula como "constantes de matriz" como esta:

{=INDICE({"red","green","blue"},COINCIDIR(VERDADERO,ESNUMERO(HALLAR({"red","green","blue"},B5)),0))}