Fórmula genérica
(=MAX(IF(criteria,ROW(rng)-MIN(ROW(rng))+1)))
Resumo
Para obter a posição da última correspondência (ou seja, última ocorrência) de um valor de pesquisa, você pode usar uma fórmula de matriz baseada nas funções IF, ROW, INDEX, MATCH e MAX. No exemplo mostrado, a fórmula em H6 é:
(=MAX(IF(names=H5,ROW(names)-MIN(ROW(names))+1)))
Onde "nomes" é o intervalo nomeado C4: C11.
Observação: esta é uma fórmula de matriz e deve ser inserida com control + shift + enter.
Explicação
A essência dessa fórmula é que construímos uma lista de números de linha para um determinado intervalo, combinando um valor, e então usamos a função MAX para obter o maior número de linha, que corresponde ao último valor correspondente. Estamos usando o intervalo nomeado "nomes" apenas por conveniência.
Trabalhando de dentro para fora, esta parte da fórmula irá gerar um conjunto relativo de números de linha:
ROW(names)-MIN(ROW(names))+1
O resultado da expressão acima é uma matriz de números como esta:
(1;2;3;4;5;6;7;8)
Observe que obtemos 8 números, correspondentes às 8 linhas da tabela. Consulte esta página para obter detalhes sobre como essa parte da fórmula funciona.
Para o propósito desta fórmula, queremos apenas números de linha para valores correspondentes, então usamos a função IF para filtrar os valores assim:
IF(names=H5,ROW(names)-MIN(ROW(names))+1)
Isso resulta em uma matriz semelhante a esta:
(1;FALSE;FALSE;4;FALSE;FALSE;7;FALSE)
Observe que esta matriz ainda contém oito itens. No entanto, apenas os números de linha onde o valor no intervalo nomeado "nomes" é igual a "amy" sobreviveram (ou seja, 1, 4, 7). Todos os outros itens da matriz são FALSOS, pois falharam no teste lógico da função IF.
Finalmente, a função IF entrega este array para a função MAX. MAX retorna o valor mais alto na matriz, o número 7, que corresponde ao último número da linha onde o nome é "amy". Assim que soubermos o último número de linha correspondente, podemos usar INDEX para recuperar um valor nessa posição.
Penúltimo, etc.
Para obter a penúltima posição, a terceira à última, etc., você pode alternar da função MIN para a função LARGE desta forma:
(=LARGE(IF(criteria,ROW(rng)-MIN(ROW(rng))+1),k))
onde k representa o "enésimo maior". Por exemplo, para obter a penúltima correspondência no exemplo acima, você pode usar:
(=LARGE(IF(names=H5,ROW(names)-MIN(ROW(names))+1),2))
Como antes, esta é uma fórmula de matriz e deve ser inserida com control + shift + enter.
