Excel XLOOKUP Retorna Correspondência Errada com Chaves Duplicadas: Correção

Você usa XLOOKUP no Excel esperando a correspondência correta, mas ele retorna o valor errado quando sua coluna de pesquisa contém chaves duplicadas. Isso acontece porque o XLOOKUP, por padrão, retorna a primeira correspondência encontrada e, quando existem duplicatas, a primeira correspondência pode não ser a desejada. Neste artigo, você aprenderá por que o XLOOKUP se comporta dessa forma com duplicatas e como forçá-lo a retornar a correspondência correta usando técnicas específicas.

Por que o XLOOKUP Retorna a Correspondência Errada com Chaves Duplicadas

O XLOOKUP pesquisa em uma matriz de pesquisa um valor especificado e retorna um valor correspondente de uma matriz de retorno. Quando a matriz de pesquisa contém chaves duplicadas, o XLOOKUP para na primeira ocorrência que encontra. Por padrão, o XLOOKUP usa um modo de pesquisa 1, que pesquisa do primeiro ao último item. Se seus dados têm chaves duplicadas e a correspondência desejada não é a primeira ocorrência, o XLOOKUP retorna a correspondência errada.

Esse comportamento é proposital e corresponde a como VLOOKUP e INDEX/MATCH também lidam com duplicatas. O problema não é um bug no XLOOKUP, mas um mal-entendido sobre como a função funciona com chaves não únicas. Para corrigir, você deve alterar a direção da pesquisa, tornar suas chaves únicas ou usar uma fórmula mais avançada que filtre duplicatas antes da correspondência.

Como os Modos de Pesquisa do XLOOKUP Afetam a Correspondência de Duplicatas

O XLOOKUP tem um quinto argumento opcional chamado search_mode. O valor padrão é 1 (pesquisar do primeiro ao último). Outras opções incluem -1 (pesquisar do último ao primeiro), 2 (pesquisa binária crescente) e -2 (pesquisa binária decrescente). Os modos de pesquisa binária exigem dados classificados e não são adequados para duplicatas não classificadas. O modo de pesquisa do último ao primeiro (-1) é útil quando você precisa da entrada duplicada mais recente, por exemplo, a transação mais recente ou a última atualização.

Quando as Duplicatas São Inevitáveis

Em muitos conjuntos de dados do mundo real, chaves duplicadas são legítimas. Por exemplo, um relatório de vendas pode ter várias linhas para o mesmo ID de produto em datas diferentes. Se você deseja corresponder à venda mais recente, precisa classificar os dados por data decrescente e usar search_mode 1, ou manter os dados como estão e usar search_mode -1 após classificar por data crescente. A correção depende de qual duplicata você precisa: a primeira, a última ou uma específica com base em outra condição.

Passos para Corrigir o XLOOKUP Quando Ele Retorna a Correspondência Errada

Os métodos a seguir ajudarão você a forçar o XLOOKUP a retornar a correspondência correta quando sua coluna de pesquisa contiver duplicatas. Escolha o método que se adequa à sua estrutura de dados e à duplicata específica que você precisa.

Método 1: Alterar o Modo de Pesquisa para Retornar a Última Correspondência

Se seus dados estão classificados de forma que a correspondência desejada seja a última ocorrência (por exemplo, a data mais recente), use search_mode -1.

1. Identifique seu intervalo de dados
   Confirme que sua matriz de pesquisa contém chaves duplicadas e que os dados estão classificados com a correspondência desejada como a última ocorrência. Por exemplo, classifique por data crescente para que a data mais recente fique na parte inferior.
2. Escreva a fórmula XLOOKUP com search_mode -1
   Use esta sintaxe: =XLOOKUP(valor_procurado, matriz_pesquisa, matriz_retorno, , , -1). O quinto argumento (se_não_encontrado) é omitido deixando-o em branco com duas vírgulas. O sexto argumento define search_mode como -1.
3. Pressione Enter e verifique o resultado
   O Excel retorna o valor da última linha correspondente na matriz de retorno. Teste com uma duplicata conhecida para confirmar que ele seleciona a linha correta.

Método 2: Criar uma Coluna Auxiliar para Tornar as Chaves Únicas

Quando você precisa de uma duplicata específica que não é nem a primeira nem a última, crie uma chave única anexando um número de índice a cada valor duplicado.

1. Adicione uma coluna auxiliar ao lado da sua matriz de pesquisa
   Insira uma nova coluna à direita da sua coluna de pesquisa. Na primeira linha de dados, insira esta fórmula: =A2&CONT.SE($A$2:A2, A2). Substitua A2 pela referência real da célula de pesquisa. Esta fórmula combina o valor original com sua contagem de ocorrências até o momento.
2. Copie a fórmula para baixo na coluna auxiliar
   Arraste a alça de preenchimento para aplicar a fórmula a todas as linhas. Cada duplicata agora tem um identificador único: por exemplo, “ProdutoA1”, “ProdutoA2”, “ProdutoA3”.
3. Construa a fórmula XLOOKUP usando a coluna auxiliar
   Use esta sintaxe: =XLOOKUP(valor_procurado & número_ocorrência, coluna_auxiliar, matriz_retorno). Substitua número_ocorrência pela duplicata específica desejada. Por exemplo, para obter a terceira ocorrência: =XLOOKUP("ProdutoA" & 3, C2:C100, B2:B100).
4. Pressione Enter e verifique
   O XLOOKUP agora pesquisa em chaves únicas e retorna a correspondência correta para a ocorrência especificada.

Método 3: Usar FILTER para Pré-Filtrar os Dados

Quando a duplicata correta é determinada por uma condição em outra coluna, use FILTER para restringir a matriz de pesquisa antes de aplicar o XLOOKUP.

1. Identifique a condição que isola a duplicata correta
   Por exemplo, você deseja o registro de venda para ProdutoA onde a coluna Status é igual a “Concluído”.
2. Escreva uma fórmula FILTER dentro do XLOOKUP
   Use esta sintaxe: =XLOOKUP(valor_procurado, FILTER(matriz_pesquisa, matriz_condição=valor_condição), FILTER(matriz_retorno, matriz_condição=valor_condição)). Por exemplo: =XLOOKUP("ProdutoA", FILTER(A2:A100, C2:C100="Concluído"), FILTER(B2:B100, C2:C100="Concluído")).
3. Pressione Enter e verifique
   O XLOOKUP agora vê apenas as linhas que atendem à condição, eliminando duplicatas indesejadas.

Se o XLOOKUP Ainda Retornar a Correspondência Errada Após a Correção Principal

XLOOKUP Retorna #N/A Mesmo que o Valor Exista

Isso geralmente acontece quando a abordagem da coluna auxiliar cria chaves que não correspondem exatamente ao valor procurado. Verifique se a fórmula da coluna auxiliar usa as referências de intervalo corretas e se o número de ocorrência no valor procurado corresponde ao formato da coluna auxiliar. Além disso, verifique se não há espaços extras. Use a função TRIM tanto no valor procurado quanto na coluna auxiliar para remover caracteres invisíveis.

XLOOKUP Retorna a Correspondência Errada Após Classificar

Quando você altera a ordem de classificação dos dados, o XLOOKUP com search_mode 1 ou -1 pode se comportar de forma diferente porque a primeira ou última ocorrência muda. Após classificar, sempre verifique se o modo de pesquisa corresponde à posição desejada. Se você precisar de uma ocorrência específica independentemente da ordem de classificação, use o método da coluna auxiliar.

XLOOKUP Funciona em uma Pasta de Trabalho, mas Não em Outra

Isso normalmente acontece quando a segunda pasta de trabalho contém padrões de duplicatas diferentes ou os dados não estão classificados como esperado. Copie a fórmula exata da pasta de trabalho que funciona e ajuste os intervalos. Certifique-se de que ambas as pastas de trabalho usem a mesma versão do Excel que suporta XLOOKUP (Excel 2021 ou Microsoft 365).

Modos de Pesquisa do XLOOKUP para Chaves Duplicadas: Comparação

Modo de Pesquisa	Comportamento com Duplicatas	Melhor Caso de Uso
1 (padrão, primeiro ao último)	Retorna a primeira linha correspondente de cima para baixo	Quando a primeira duplicata é a correspondência desejada
-1 (último ao primeiro)	Retorna a última linha correspondente de baixo para cima	Quando a última duplicata é a correspondência desejada (ex.: data mais recente)
2 (binário crescente)	Exige dados classificados; retorna qualquer correspondência (indefinido com duplicatas não classificadas)	Grandes conjuntos de dados classificados com chaves únicas apenas
-2 (binário decrescente)	Exige dados classificados; retorna qualquer correspondência (indefinido com duplicatas não classificadas)	Grandes conjuntos de dados classificados com chaves únicas apenas

Agora você pode forçar o XLOOKUP a retornar a correspondência correta mesmo quando sua coluna de pesquisa contém chaves duplicadas. Comece identificando se você precisa da primeira, última ou de uma duplicata específica e, em seguida, aplique o método correspondente. Para conjuntos de dados onde duplicatas são inevitáveis, considere usar a coluna auxiliar com COUNTIF para criar chaves únicas. Se você trabalha frequentemente com chaves duplicadas, explore a função FILTER para pré-filtrar seus dados antes da correspondência, o que lhe dá controle total sobre qual linha o XLOOKUP avalia.