Fórmula genérica
=LOOKUP(2,1/(ISNUMBER(FIND(filename,range))),range)
Resumo
Para consultar a versão mais recente do arquivo em uma lista, você pode usar uma fórmula baseada na função LOOKUP junto com as funções ISNUMBER e FIND. No exemplo mostrado, a fórmula na célula G7 é:
=LOOKUP(2,1/(ISNUMBER(FIND(G6,files))),files)
onde "arquivos" é o intervalo nomeado B5: B11.
Contexto
Neste exemplo, temos várias versões de arquivo listadas em uma tabela com data e nome de usuário. Observe que os nomes dos arquivos são repetidos com um contador no final como um número de revisão - 001, 002, 003, etc.
Dado um nome de arquivo, queremos recuperar o nome da última ou da última revisão. Existem dois desafios:
- O desafio é que os códigos de versão no final dos nomes dos arquivos tornam mais difícil corresponder ao nome do arquivo.
- Por padrão, as fórmulas de correspondência do Excel retornarão a primeira correspondência, não a última.
Para superar esses desafios, precisamos usar algumas técnicas complicadas.
Explicação
Esta fórmula usa a função LOOKUP para localizar e recuperar o último nome de arquivo correspondente. O valor de pesquisa é 2 e o lookup_vector é criado com este:
1/(ISNUMBER(FIND(G6,files)))
Dentro deste trecho, a função FIND procura o valor em G6 dentro do intervalo nomeado "arquivos" (B5: B11). O resultado é uma matriz como esta:
(1;#VALUE!;1;1;#VALUE!;#VALUE!;1)
Aqui, o número 1 representa uma correspondência e o erro #VALUE representa um nome de arquivo não correspondente. Essa matriz vai para a função ISNUMBER e sai assim:
(TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;TRUE)
Os valores de erro agora são FALSE, e o número 1 agora é TRUE. Isso supera o desafio nº 1, agora temos uma matriz que mostra claramente quais arquivos na lista contêm o nome do arquivo de interesse.
Em seguida, a matriz é usada como denominador com 1 como numerador. O resultado é o seguinte:
(1;#DIV/0!;1;1;#DIV/0!;#DIV/0!;1)
que vai para LOOKUP como lookup_vector. Esta é uma solução complicada para o desafio nº 2. A função LOOKUP opera apenas no modo de correspondência aproximada e ignora automaticamente os valores de erro. Isso significa que com 2 como valor de pesquisa, VLOOKUP tentará encontrar 2, falhará e voltará ao número anterior (neste caso, correspondendo ao último 1 na posição 7). Finalmente, LOOKUP usa 7 como um índice para recuperar o 7º arquivo na lista de arquivos.
Tratamento de pesquisas em branco
Estranhamente, a função FIND retorna 1 se o valor de pesquisa for uma string vazia (""). Para se proteger contra uma correspondência falsa, você pode agrupar a fórmula em IF e testar uma pesquisa vazia:
=IF(G6"",LOOKUP(2,1/(ISNUMBER(FIND(G6,files))),files),"")
