Fórmula genérica
(=INDEX(things,MATCH(TRUE,ISNUMBER(SEARCH(things,A1)),0)))
Resumen
Para verificar una celda en busca de una de varias cosas y devolver la primera coincidencia encontrada en la lista, puede usar una fórmula INDICE / COINCIDIR que usa BUSCAR o ENCONTRAR para ubicar una coincidencia. En el ejemplo que se muestra, la fórmula en C5 es:
(=INDEX(things,MATCH(TRUE,ISNUMBER(SEARCH(things,B5)),0)))
donde "cosas" es el rango con nombre E5: E9.
Nota: esta es una fórmula de matriz y debe ingresarse con Control + Shift + Enter.
Explicación
En este ejemplo, tenemos una lista de colores en un rango con nombre llamado "cosas" (E5: E9). Queremos comprobar el texto de la columna B para ver si contiene alguno de estos colores. Si es así, queremos devolver el nombre del primer color encontrado.
Trabajando desde adentro hacia afuera, esta fórmula usa la función ISNUMBER y la función SEARCH para buscar el texto en B5 para cada color listado en "cosas" como esta:
ISNUMBER(SEARCH(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. De lo contrario, la expresión devuelve FALSE.
Cuando le damos a esta BÚSQUEDA una lista de cosas (en lugar de una cosa) nos devolverá una matriz 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!)
Luego, la función ISNUMBER convierte los resultados en 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:
(FALSE;FALSE;TRUE;FALSE;FALSE)
Esta matriz se devuelve a la función COINCIDIR como argumento de 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 primera posición VERDADERA encontrada. Este valor se introduce en la función INDICE como el número de fila, con el rango con nombre "cosas" 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 valores en la fórmula como "constantes de matriz" como esta:
(=INDEX(("red","green","blue"),MATCH(TRUE,ISNUMBER(SEARCH(("red","green","blue"),B5)),0)))
Obtener la primera coincidencia en la celda
El lenguaje aquí es bastante confuso, pero la fórmula anterior devolverá la primera coincidencia que se encuentra en la lista de cosas a buscar. Si, en cambio, desea devolver la primera coincidencia encontrada en la celda que se está probando, puede probar una fórmula como esta:
=INDEX(things,MATCH(AGGREGATE(15,6,SEARCH(things,A1),1),SEARCH(things,A1),0))
En esta versión de la fórmula, la función COINCIDIR está configurada para buscar el resultado de este fragmento:
AGGREGATE(15,6,SEARCH(things,A1),1) // get min value
que usa la función AGREGAR para obtener el valor mínimo en los resultados devueltos por SEARCH. Necesitamos AGREGAR aquí, porque la matriz entrante probablemente contendrá errores (devueltos por SEARCH cuando no se encuentran cosas), y necesitamos una función que ignore esos errores y aún nos dé el valor numérico mínimo.
El resultado de AGGREGATE se devuelve directamente a MATCH como valor de búsqueda, junto con la misma matriz devuelta por SEARCH. El resultado final es la primera coincidencia que se encuentra en la celda, no la primera coincidencia que se encuentra en la lista de cosas.
