EXCEL: Obtener la última celda contiene coincidencias

Para verificar una celda de una de varias cosas y devolver la última coincidencia encontrada en la lista, puede usar una fórmula basada en las funciones BUSCAR y HALLAR. En el caso de que se encuentren varias coincidencias, la fórmula devolverá la última coincidencia de la lista de "cosas". En el ejemplo que se muestra, la fórmula en C5 es:

=BUSCAR(2,1/HALLAR(things,B5),things)

Cómo funciona esta fórmula

Contexto: tiene una lista de cosas en el rango nombrado "cosas" (E5: E8), y desea verificar las celdas en la columna B para ver si contienen estas cosas. Si es así, desea devolver el último elemento de "cosas" que se encontraron.

En esta fórmula, la función HALLAR se usa para buscar celdas en la columna B así:

HALLAR(things,B5)

Cuando HALLAR encuentra una coincidencia, devuelve la posición de la coincidencia en la celda que se está buscando. Cuando la búsqueda no puede encontrar una coincidencia, devuelve el error #VALOR. Debido a que le estamos dando a SEARH más de una cosa para buscar, arrojará más de un resultado. En el ejemplo que se muestra, HALLAR devuelve una matriz de resultados como este:

{8;24;#VALUE!;#VALUE!}

Esta matriz se utiliza como divisor para el número 1. El resultado es una matriz compuesta de errores y valores decimales. Los errores representan cosas no encontradas y los valores decimales representan cosas encontradas. En el ejemplo que se muestra, la matriz se ve así:

{0.125;0.0416666666666667;#VALUE!;#VALUE!}

Esta matriz sirve como "lookup_vector" para la función BUSCAR. El valor de búsqueda se proporciona como el número 2, y el vector de resultado es el rango "cosas" con nombre. Esta es la parte inteligente.

La fórmula está construida de tal manera que el vector de búsqueda nunca contendrá un valor mayor que 1, mientras que el valor de búsqueda es 2. Esto significa que el valor de búsqueda nunca se encontrará. En este caso, BUSCAR coincidirá con el último valor numérico encontrado en la matriz, que corresponde a la última "cosa" encontrada por HALLAR.

Finalmente, usando las "cosas" de rango con nombre suministradas como el vector de resultados, BUSCAR devuelve lo último que encontró.

Con valores codificados

El uso de un rango como "cosas" facilita la modificación de la lista de términos de búsqueda (y agrega más términos de búsqueda), pero no es un requisito. También puede codificar valores directamente en la fórmula de esta manera:

=BUSCAR(2,1/HALLAR({"red","blue","green"},B5),{"red","blue","green"})