Fórmula genérica
=LOOKUP(2,1/SEARCH(things,A1),things)
Resumen
Para verificar una celda para una de varias cosas y devolver la última coincidencia encontrada en la lista, puede usar una fórmula basada en las funciones BÚSQUEDA y BÚSQUEDA. 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:
=LOOKUP(2,1/SEARCH(things,B5),things)
Explicación
Contexto: tiene una lista de cosas en el rango denominado "cosas" (E5: E8) y desea comprobar las celdas en la columna B para ver si contienen estas cosas. Si es así, desea devolver el último elemento de "cosas" que se encontró.
En esta fórmula, la función BÚSQUEDA se usa para buscar celdas en la columna B de esta manera:
SEARCH(things,B5)
Cuando SEARCH encuentra una coincidencia, devuelve la posición de la coincidencia en la celda que se busca. Cuando la búsqueda no puede encontrar una coincidencia, devuelve el error #VALOR. Debido a que le damos a SEARH más de una cosa para buscar, devolverá más de un resultado. En el ejemplo que se muestra, SEARCH devuelve una matriz de resultados como esta:
(8;24;#VALUE!;#VALUE!)
Esta matriz se usa luego como divisor del 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 BÚSQUEDA. El valor de búsqueda se proporciona como el número 2 y el vector de resultado es el rango denominado "cosas". Ésta 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, LOOKUP coincidirá con el último valor numérico encontrado en la matriz, que corresponde a la última "cosa" encontrada por SEARCH.
Finalmente, utilizando el rango con nombre "cosas" proporcionado como vector de resultado, BUSCAR devuelve lo último que se encuentra.
Con valores codificados
El uso de un rango como "cosas" facilita la modificación de la lista de términos de búsqueda (y agregar 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:
=LOOKUP(2,1/SEARCH(("red","blue","green"),B5),("red","blue","green"))
