Fórmula genérica
(=MAX(IF(ISERROR(SEARCH(H5&"*",files)),0,ROW(files)-ROW(INDEX(files,1,1))+1)))
Resumo
Para encontrar a posição (linha) da última revisão do arquivo em uma tabela, você pode usar uma fórmula baseada em várias funções do Excel: MAX, IF, ISERROR, ROW e INDEX.
No exemplo mostrado, a fórmula na célula H6 é:
(= MAX (SE (ISERROR (SEARCH (H5 & "*", arquivos)), 0, ROW (arquivos) -ROW (INDEX (arquivos, 1,1)) + 1)))
onde "arquivos" é o intervalo nomeado C4: C11.
Observação: esta é uma fórmula de matriz e deve ser inserida com control + shift + enter.
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, exceto para o código anexado no final para representar a versão ("CA", "CB", "CC", "CD", etc.).
Para um determinado arquivo, queremos localizar a posição (número da linha) para a última revisão. Este é um problema complicado, porque os códigos de versão no final dos nomes dos arquivos tornam mais difícil a correspondência com o nome do arquivo. Além disso, por padrão, as fórmulas de correspondência do Excel retornarão a primeira correspondência, não a última, portanto, precisamos contornar esse desafio com algumas técnicas complicadas.
Explicação
No centro desta fórmula, construímos uma lista de números de linhas para um determinado arquivo. Em seguida, usamos a função MAX para obter o maior número de linha, que corresponde à última revisão (última ocorrência) desse arquivo.
Para localizar todas as ocorrências de um determinado arquivo, utilizamos a função SEARCH, configurada com o curinga asterisco (*) para corresponder ao nome do arquivo, ignorando os códigos de versão. SEARCH gerará um erro VALUE quando o texto não for encontrado, então envolvemos a pesquisa em ISERROR:
ISERROR(SEARCH(H5&"*",files))
Isso resulta em uma matriz de valores TRUE e FALSE como este:
(FALSO; VERDADEIRO; FALSO; FALSO; VERDADEIRO; VERDADEIRO; FALSO; VERDADEIRO)
É confuso, mas TRUE representa um erro (texto não encontrado) e FALSE representa uma correspondência. Este resultado da matriz é alimentado na função IF como o teste lógico. Para valor se TRUE, usamos zero, e para valor se verdadeiro, fornecemos este código, que gera números de linha relativos para o intervalo com o qual estamos trabalhando:
ROW(files)-ROW(INDEX(files,1,1))+1)
A função IF então retorna uma matriz de valores como este:
(1; 0; 3; 4; 0; 0; 7; 0)
Todos os números, exceto zero, representam correspondências para "filename1" - isto é, o número da linha dentro do intervalo nomeado "arquivos" onde "filename1" aparece.
Por fim, usamos a função MAX para obter o valor máximo neste array, que é 7 neste exemplo.
Use INDEX com este número de linha para recuperar informações relacionadas à última revisão (ou seja, nome completo do arquivo, data, usuário, etc).
Sem intervalo nomeado
Intervalos nomeados tornam rápido e fácil configurar uma fórmula mais complexa, uma vez que você não precisa inserir endereços de células manualmente. No entanto, neste caso, estamos usando uma função extra (INDEX) para obter a primeira célula do intervalo nomeado "arquivos", o que complica um pouco as coisas. Sem o intervalo nomeado, a fórmula se parece com isto:
(=MAX(IF(ISERROR(SEARCH(H5&"*",C4:C11)),0,ROW(C4:C11)-ROW(C4)+1)))
