Fórmula genérica
(=INDEX(results,MATCH(TRUE,ISNUMBER(SEARCH(things,A1)),0)))
Resumen
Para probar una celda para una de varias cadenas y devolver un resultado personalizado para la primera coincidencia encontrada, puede usar una fórmula INDICE / COINCIDIR basada en la función BUSCAR. En el ejemplo que se muestra, la fórmula en C5 es:
(=INDEX(results,MATCH(TRUE,ISNUMBER(SEARCH(things,B5)),0)))
donde las cosas (E5: E8) y los resultados (F5: F8) se denominan rangos.
Esta es una fórmula de matriz y debe ingresarse con Control + Shift + Enter.
Explicación
Esta fórmula usa dos rangos con nombre: cosas y resultados . Si está transfiriendo esta fórmula directamente, asegúrese de usar rangos con los mismos nombres (definidos en función de sus datos). Si no desea utilizar rangos con nombre, utilice referencias absolutas.
El núcleo de esta fórmula es este fragmento:
ISNUMBER(SEARCH(things,B5)
Esto se basa en otra fórmula (explicada en detalle aquí) que busca una sola subcadena en una celda. Si la celda contiene la subcadena, la fórmula devuelve VERDADERO. Si no, la fórmula devuelve FALSO.
Debido a que le estamos dando a la función de BÚSQUEDA más de una cosa para buscar, en las cosas del rango con nombre , nos dará más el resultado, en una matriz que se ve así:
(#VALUE!;9;#VALUE!;#VALUE!)
Los números representan coincidencias en cosas , los errores representan elementos que no se encontraron.
Para simplificar la matriz, usamos la función ESNÚMERO para convertir todos los elementos de la matriz a VERDADERO o FALSO. Cualquier número válido se convierte en VERDADERO y cualquier error (es decir, algo que no se encuentra) se vuelve FALSO. El resultado es una matriz como esta:
(FALSE;TRUE;FALSE;FALSE)
que entra en la función COINCIDIR como el argumento lookup_array, con un lookup_value de TRUE:
MATCH(TRUE,(FALSE;TRUE;FALSE;FALSE),0) // returns 2
COINCIDIR luego devuelve la posición del primer VERDADERO encontrado, 2 en este caso.
Por último, se utiliza la función de índice para recuperar un resultado de la gama nombrados resultados en esa misma posición:
=INDEX(results,2) // returns "found red"
Puede personalizar el rango de resultados con los valores que tengan sentido en su caso de uso.
Evitar coincidencias falsas
Un problema con este enfoque con el enfoque ISNUMBER + SEARCH es que puede obtener coincidencias falsas de coincidencias parciales dentro de palabras más largas. Por ejemplo, si intenta hacer coincidir "dr", también puede encontrar "Andrea", "bebió", "goteo", etc. ya que "dr" aparece dentro de estas palabras. Esto sucede porque SEARCH automáticamente hace una coincidencia de "tipo contiene".
Para una solución rápida, puede ajustar las palabras de búsqueda en caracteres de espacio (es decir, "dr" o "dr") para evitar encontrar "dr" en otra palabra. Pero esto fallará si "dr" aparece primero o último en una celda.
Si necesita una solución más sólida, una opción es normalizar el texto primero en una columna auxiliar y agregar un espacio inicial y final. Luego, use la fórmula de esta página en el texto de la columna de ayuda, en lugar del texto original.
