Pesquisando o último valor correspondente

A maioria das buscas retorna a primeira correspondência encontrada. Mas, às vezes, você quer a última: o preço mais recente de um produto, a atualização de status mais recente ou o registro final de um cliente.

Quando uma lista cresce com o tempo e a mesma chave aparece várias vezes, a linha mais abaixo geralmente é a mais atual. Um VLOOKUP padrão ou um MATCH com correspondência exata insistirá em selecionar a primeira linha.

Nesta lição, você verá várias maneiras confiáveis de obter o valor correspondente à última ocorrência.

MATCH(value, range, 0) percorre do início ao fim e para na primeira ocorrência exata. Se "Apple" aparecer nas linhas 2, 5 e 9, MATCH retornará 2.

Isso é perfeito quando as chaves são exclusivas, mas ignora as linhas mais recentes. Para chegar à última ocorrência, precisamos de uma técnica que pesquise de baixo para cima ou que retorne a posição da última correspondência.

Se você tiver uma versão moderna do Excel ou do Google Sheets, XLOOKUP facilita essa tarefa. O quinto e o sexto argumentos controlam o modo de correspondência e a direção da pesquisa.

Informe -1 como argumento do modo de pesquisa para pesquisar da última ocorrência até a primeira. XLOOKUP então retorna o valor associado à chave correspondente na linha mais abaixo.

Aqui, ele pesquisa o produto de G1 em A2:A10 e retorna o preço correspondente de B2:B10, começando a pesquisa pela parte inferior.

Em planilhas mais antigas, um truque conhecido usa LOOKUP com o número 2 e uma divisão inteligente por uma condição.

A expressão 1/(A2:A10=G1) produz 1 nas linhas correspondentes e um erro de divisão nas que não correspondem. LOOKUP, ao pesquisar 2 — um valor maior que qualquer valor presente — passa pelos erros e chega ao último 1 válido, retornando o valor correspondente de B2:B10.

Analise passo a passo 1/(A2:A10=G1):

• As linhas em que a chave corresponde produzem 1/TRUE = 1.
• As linhas que não correspondem produzem 1/FALSE = um erro #DIV/0!.

LOOKUP ignora os erros e, quando não encontra o destino pesquisado (2), retorna o resultado alinhado à última entrada sem erro. Como todas as correspondências são 1, o último 1 vence; assim, você obtém o valor da última linha correspondente.

Você também pode continuar usando a família INDEX-MATCH. A ideia é encontrar a posição da última correspondência e depois fornecê-la a INDEX.

Usando o mesmo truque da divisão dentro de MATCH, pesquise 2 em 1/(A2:A10=G1) para obter a posição da linha da última correspondência. Forneça essa posição a INDEX sobre a coluna de retorno.

Quando o terceiro argumento de MATCH é omitido, o padrão é 1, que significa correspondência aproximada em dados crescentes. MATCH então procura o maior valor menor ou igual a 2.

A matriz 1/(A2:A10=G1) contém apenas 1s e erros. O maior valor menor ou igual a 2 é 1, e MATCH retorna a posição do último 1 desse tipo. Essa posição é exatamente a última linha correspondente.

Suponha que A2:A10 liste os status de pedidos de "Order-7" registrados ao longo do tempo, e que B2:B10 contenha o texto do status. "Order-7" aparece nas linhas 3, 6 e 9.

• A matriz de correspondência marca as linhas 3, 6 e 9 com 1, e as demais com erros.
• MATCH(2, ...) retorna 9 como posição, contando desde o início do intervalo: a última correspondência.
• INDEX retorna o status dessa linha final, que é o mais recente.

Qual abordagem você deve usar?

• XLOOKUP com -1: a opção mais simples e legível, se o seu aplicativo for compatível.
• LOOKUP(2, 1/...): funciona em quase todos os lugares, sem exigir uma versão específica.
• INDEX-MATCH(2, 1/...): útil quando você também precisa da posição ou quer retornar um valor de outra coluna.

Os três métodos fornecem a mesma resposta; escolha com base nas suas ferramentas e no nível de legibilidade desejado para a fórmula.

Fique atento a estes problemas:

• Tamanhos de intervalo incompatíveis: o intervalo da condição e o intervalo de retorno devem ter a mesma altura, caso contrário as linhas ficarão desalinhadas.
• Duplicatas ocultas: espaços no final podem fazer com que "Apple " seja diferente de "Apple"; primeiro limpe o texto com TRIM.
• Nenhuma correspondência: o truque retorna um erro se nada corresponder. Envolva-o em IFERROR para oferecer uma alternativa amigável.

Você pode combinar o truque da última correspondência com duas condições. Multiplique os testes das condições dentro da divisão, para que apenas as linhas que atendam às duas chaves produzam 1.

Por exemplo, encontre o preço mais recente em que o produto seja igual a G1 e a região seja igual a G2. O truque LOOKUP(2, ...) ainda chegará à última linha que atende aos critérios.

Isso é útil para registros com marcação de data e hora, nos quais o mesmo produto aparece em várias regiões.

Para retornar o valor da última correspondência em vez da primeira:

• Use XLOOKUP(..., -1) para pesquisar de baixo para cima, quando disponível.
• Use o truque clássico LOOKUP(2, 1/(range=key), result) em qualquer versão.
• Use INDEX(result, MATCH(2, 1/(range=key))) quando também precisar da posição.

Lembre-se de manter os intervalos do mesmo tamanho, remover espaços indesejados e envolver a fórmula em IFERROR para garantir segurança.