Fórmula genérica
(=INDEX(categories,MATCH(TRUE,ISNUMBER(SEARCH(keywords,text)),0)))
Resumen
Para categorizar texto usando palabras clave con una coincidencia "contiene", puede usar la función BUSCAR, con la ayuda de INDICE y COINCIDIR. En el ejemplo que se muestra, la fórmula en C5 es:
(=INDEX(categories,MATCH(TRUE,ISNUMBER(SEARCH(keywords,B5)),0)))
donde palabras clave es el rango con nombre E5: E14 y categorías es el rango con nombre F5: F14.
Nota: esta es una fórmula de matriz y debe ingresarse con control + shift + enter.
Explicación
Básicamente, esta es una función INDICE y COINCIDIR.
Dentro de la función COINCIDIR, usamos la función BÚSQUEDA para buscar celdas en la columna B para cada palabra clave listada en las palabras clave del rango con nombre (E5: E14):
SEARCH(keywords,B5)
Debido a que estamos buscando varios elementos (en las palabras clave del rango con nombre ), obtendremos varios resultados como este:
(#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;24;#VALUE!;#VALUE!;#VALUE!)
¡El valor! Se produce un error cuando SEARCH no puede encontrar el texto. Cuando SEARCH encuentra una coincidencia, devuelve un número que corresponde a la posición del texto dentro de la celda.
Para cambiar estos resultados a un formato más utilizable, usamos la función ESNUMERO, que convierte todos los valores a VERDADERO / FALSO así:
(FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE)
Esta matriz entra en la función COINCIDIR como lookup_array, con lookup_value establecido como TRUE. COINCIDIR devuelve la posición del primer VERDADERO que encuentra en la matriz (7 en este caso) que se proporciona a la función INDICE como número_fila:
=INDEX(categories,7)
INDICE devuelve el séptimo elemento de las categorías , "Auto", como resultado final.
Con XLOOKUP
Con la función XLOOKUP, esta fórmula se puede simplificar un poco. XLOOKUP puede utilizar la misma lógica utilizada dentro de la función COINCIDIR anterior, por lo que la fórmula equivalente es:
=XLOOKUP(TRUE,ISNUMBER(SEARCH(keywords,B5)),categories)
XLOOKUP ubica el primer VERDADERO en la matriz y devuelve el valor correspondiente de las categorías .
Evitar coincidencias falsas
Un problema con este enfoque es que puede obtener coincidencias falsas de subcadenas que aparecen dentro de palabras más largas. Por ejemplo, si intenta hacer coincidir "dr", también puede encontrar "Andrea", "beber", "seco", etc., ya que "dr" aparece dentro de estas palabras. Esto sucede porque SEARCH automáticamente hace una coincidencia "contiene".
Para un truco rápido, puede agregar espacio alrededor de las palabras de búsqueda (es decir, "dr" o "dr") para evitar encontrar "dr" en otra palabra. Pero esto fallará si "dr" aparece primero o último en una celda, o aparece con puntuación, etc.
Si necesita una solución más precisa, una opción es normalizar el texto primero en una columna auxiliar, teniendo cuidado de agregar también un espacio inicial y final. Luego, puede buscar palabras completas rodeadas de espacios.
