Recuperar el primer valor coincidente en una celda frente a una lista

Supongamos que tienes una lista de palabras clave y quieres recuperar la primera palabra clave que aparece en una celda específica, aunque esa celda contenga varios valores más. Para lograrlo, deberás utilizar una fórmula combinando las funciones INDEX y MATCH junto con AGGREGATE y SEARCH.

¿Cómo recuperar el primer valor coincidente en una celda frente a una lista?

Para obtener la primera palabra clave coincidente en una celda respecto a la lista de palabras clave, como se muestra en la tabla anterior, es necesario realizar una coincidencia de contenido en lugar de una coincidencia exacta. Para ello, puedes utilizar la función SEARCH para obtener las posiciones numéricas de las palabras clave que aparecen en la celda y pasarlas a la función AGGREGATE. Luego, AGGREGATE devolverá el número más pequeño configurando su function_num en15 y el argumento ref2 en1. Después, usa MATCH para localizar el valor más pequeño y pasa ese número de posición a INDEX para recuperar el valor correspondiente.

Sintaxis genérica

=INDEX(keyword_rng,MATCH(AGGREGATE(15,6,SEARCH(keyword_rng,lookup_cell),1),SEARCH(keyword_rng,lookup_cell),0))

√ Nota: Esta es una fórmula de matriz que requiere ser introducida con Ctrl + Shift + Enter.

Para recuperar la primera palabra clave coincidente que aparece en la celda B5 respecto a la columna de palabras clave, copia o introduce la siguiente fórmula en la celda C5 y pulsa Ctrl + Shift + Enter para obtener el resultado:

=INDEX($E$5:$E$7,MATCH(AGGREGATE(15,6,SEARCH($E$5:$E$7,B5),1),SEARCH($E$5:$E$7,B5),0))

√ Nota: Los signos de dólar ($) indican referencias absolutas, lo que significa que el keyword_rng en la fórmula no cambiará al mover o copiar la fórmula a otras celdas. Sin embargo, no se añaden signos de dólar a lookup_cell ya que se desea que sea dinámica. Después de introducir la fórmula, arrastra el controlador de relleno hacia abajo para aplicarla a las celdas inferiores.

Explicación de la fórmula

=INDEX($E$5:$E$7,MATCH(AGGREGATE(15,6,SEARCH($E$5:$E$7,B5)SEARCH($E$5:$E$7,B5),1),SEARCH($E$5:$E$7,B5)SEARCH($E$5:$E$7,B5),0))

• SEARCH($E$5:$E$7,B5): La función SEARCH devuelve la posición de cada palabra clave del rango $E$5:$E$7 cuando se encuentra, en formato numérico, y el error #VALUE! si no se encuentra. El resultado sería un array como este: {15;11;#VALUE!}.
• AGGREGATE(15,6,AGGREGATE(15,6,SEARCH($E$5:$E$7,B5),1),1) = AGGREGATE(15,6,AGGREGATE(15,6,{15;11;#VALUE!},1),1): La función AGGREGATE con function_num de 15 y opción 6 devolverá el valor más pequeño del array según el argumento ref2 1, ignorando los valores de error. Así, el resultado sería 11.
• MATCH(MATCH(AGGREGATE(15,6,SEARCH($E$5:$E$7,B5)SEARCH($E$5:$E$7,B5),1),,SEARCH($E$5:$E$7,B5)SEARCH($E$5:$E$7,B5),0),0) = MATCH(MATCH(11,,{15;11;#VALUE!},0),0): El match_type 0 obliga a la función MATCH a realizar una coincidencia exacta y devolver la posición de 11 en el array {15;11;#VALUE!}. Por lo tanto, la función devolverá 2.
• INDEX($E$5:$E$7,MATCH(AGGREGATE(15,6,SEARCH($E$5:$E$7,B5)SEARCH($E$5:$E$7,B5),1),SEARCH($E$5:$E$7,B5)SEARCH($E$5:$E$7,B5),0)) = INDEX($E$5:$E$7,2): La función INDEX devuelve el segundo valor del rango $E$5:$E$7, que es bbb.

Observación

• Si no hay palabras clave en una celda, se devolverá un error #NUM!.
• La fórmula no distingue entre mayúsculas y minúsculas. Para realizar una coincidencia que sí lo haga, simplemente reemplaza la función SEARCH por FIND.

Funciones relacionadas

Fórmulas relacionadas