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

Suponga que tiene una lista de palabras clave y desea recuperar la primera que aparezca en una celda específica, incluso si dicha celda contiene otros valores. Para lograrlo, deberá usar una fórmula de INDEX y MATCH, con la ayuda de las funciones AGGREGATE y SEARCH.

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

Para obtener la primera palabra clave coincidente en una celda frente a la lista de palabras clavetal como se muestra en la tabla anterior, debes realizar una coincidencia parcial en lugar de una coincidencia exacta. Para ello, utiliza la función SEARCH para pasar las posiciones numéricas de las palabras clave que aparecen en la celda a la función AGGREGATE. A continuación, AGGREGATE obtendrá el número más pequeño al establecer su function_num en 15 y el argumento ref2 en 1. 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 matricial que requiere pulsar Ctrl+Mayús+Intro.

Para recuperar la primera palabra clave coincidente que aparece en la celda B5 frente a la columna Palabras clave, copie o introduzca la siguiente fórmula en la celda C5 y pulse Ctrl+Mayús+Intropara 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_rngde la fórmula no cambiará al moverla o copiarla a otras celdas. Sin embargo, no se han añadido signos de dólar a la lookup_cellporque se desea que sea dinámica. Tras introducir la fórmula, arrastre 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),1),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, expresada mediante números, y el error #VALUE! si no se encuentra. El resultado es un array como este: {15;11;#VALUE!}.
• AGGREGATE(15,6,)SEARCH($E$5:$E$7,B5),1)=AGGREGATE(15,6,){15;11;#VALUE!},1):La función AGGREGATE con un function_num de 15 y una opción de 6 devuelve el valor más pequeño del array según el argumento ref2 (1), ignorando los valores de error. Por tanto, el fragmento devuelve 11.
• MATCH()AGGREGATE(15,6,)SEARCH($E$5:$E$7,B5),1),SEARCH($E$5:$E$7,B5)=MATCH()11,{15;11;#VALUE!},0): La 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!}. Así, la función devuelve 2.
• INDEX($E$5:$E$7,)MATCH()AGGREGATE(15,6,)SEARCH($E$5:$E$7,B5),1),SEARCH($E$5:$E$7,B5)) = INDEX($E$5:$E$7,2):La función INDEX devuelve entonces el 2.º valor del rango $E$5:$E$7, que es bbb.

Observación

• Si no hay palabras clave en una celda, se devolverá el error #NUM!.
• La fórmula no distingue entre mayúsculas y minúsculas. Para realizar una coincidencia que sí las distinga, basta con sustituir la función SEARCH por la función FIND.

Funciones relacionadas

Fórmulas relacionadas