Fórmula genérica
=LOOKUP(2,1/(ISNUMBER(FIND(filename,range))),range)
Resumen
Para buscar la última versión del archivo en una lista, puede utilizar una fórmula basada en la función BUSCAR junto con las funciones ESNUMERO y ENCONTRAR. En el ejemplo que se muestra, la fórmula en la celda G7 es:
=LOOKUP(2,1/(ISNUMBER(FIND(G6,files))),files)
donde "archivos" es el rango con nombre B5: B11.
Contexto
En este ejemplo, tenemos varias versiones de archivos enumeradas en una tabla con una fecha y un nombre de usuario. Tenga en cuenta que los nombres de archivo se repiten con un contador al final como número de revisión: 001, 002, 003, etc.
Dado un nombre de archivo, queremos recuperar el nombre de la última o última revisión. Hay dos desafíos:
- El desafío es que los códigos de versión al final de los nombres de archivo hacen que sea más difícil hacer coincidir el nombre del archivo.
- De forma predeterminada, las fórmulas de coincidencia de Excel devolverán la primera coincidencia, no la última coincidencia.
Para superar estos desafíos, debemos utilizar algunas técnicas complicadas.
Explicación
Esta fórmula utiliza la función BUSCAR para buscar y recuperar el último nombre de archivo coincidente. El valor de búsqueda es 2 y el lookup_vector se crea con esto:
1/(ISNUMBER(FIND(G6,files)))
Dentro de este fragmento, la función BUSCAR busca el valor en G6 dentro del rango con nombre "archivos" (B5: B11). El resultado es una matriz como esta:
(1;#VALUE!;1;1;#VALUE!;#VALUE!;1)
Aquí, el número 1 representa una coincidencia y el error #VALUE representa un nombre de archivo que no coincide. Esta matriz entra en la función ISNUMBER y sale así:
(TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;TRUE)
Los valores de error ahora son FALSOS y el número 1 ahora es VERDADERO. Esto supera el desafío n. ° 1, ahora tenemos una matriz que muestra claramente qué archivos de la lista contienen el nombre de archivo de interés.
A continuación, la matriz se utiliza como denominador con 1 como numerador. El resultado se ve así:
(1;#DIV/0!;1;1;#DIV/0!;#DIV/0!;1)
que entra en LOOKUP como lookup_vector. Esta es una solución complicada para el desafío n. ° 2. La función BÚSQUEDA opera solo en modo de coincidencia aproximada e ignora automáticamente los valores de error. Esto significa que con 2 como valor de búsqueda, BUSCARV intentará encontrar 2, fallará y volverá al número anterior (en este caso, coincidiendo con el último 1 en la posición 7). Finalmente, LOOKUP usa 7 como un índice para recuperar el séptimo archivo en la lista de archivos.
Manejo de búsquedas en blanco
Curiosamente, la función FIND devuelve 1 si el valor de búsqueda es una cadena vacía (""). Para protegerse contra una coincidencia falsa, puede envolver la fórmula en IF y probar una búsqueda vacía:
=IF(G6"",LOOKUP(2,1/(ISNUMBER(FIND(G6,files))),files),"")
