Você precisa encontrar um único valor em uma lista no Excel, mas sua fórmula retorna vários resultados ou um erro. Isso acontece quando seu intervalo de pesquisa contém valores duplicados. A função XLOOKUP foi projetada para lidar com isso por padrão. Este artigo explica como usar XLOOKUP para retornar de forma confiável apenas o primeiro item correspondente.
Principais Conclusões: Usando XLOOKUP para a Primeira Correspondência
- XLOOKUP com argumentos padrão: Encontra e retorna automaticamente o valor da primeira linha correspondente em seus dados.
- Argumento match_mode definido como 0: Garante que a função busque uma correspondência exata, que é o uso mais comum.
- Argumento search_mode omitido ou definido como 1: Direciona a função a pesquisar do primeiro ao último item, garantindo que a primeira correspondência seja encontrada.
Como o XLOOKUP Encontra a Primeira Correspondência por Padrão
XLOOKUP é um substituto moderno para funções como VLOOKUP e HLOOKUP. Seu design principal é pesquisar um valor de pesquisa dentro de uma matriz de pesquisa. Quando encontra uma correspondência, retorna um valor correspondente de uma matriz de retorno localizada na mesma posição. Um comportamento importante é que o XLOOKUP interrompe a busca assim que encontra o primeiro valor correspondente na matriz de pesquisa. Você não precisa classificar seus dados para que isso funcione corretamente. A função pesquisa do topo do intervalo especificado para baixo por padrão, tornando a primeira ocorrência encontrada o valor retornado.
Passos para Escrever uma Fórmula XLOOKUP para a Primeira Correspondência
A sintaxe básica do XLOOKUP é =XLOOKUP(valor_procurado, matriz_pesquisa, matriz_retorno, [se_não_encontrado], [modo_correspondência], [modo_pesquisa]). Para retornar a primeira correspondência, normalmente você só precisa dos três primeiros argumentos. Os argumentos opcionais controlam o comportamento para valores ausentes, correspondências aproximadas e direção da pesquisa.
- Identifique seu valor de pesquisa
Selecione a célula que contém o valor que deseja encontrar ou digite o valor diretamente na fórmula entre aspas. - Defina a matriz de pesquisa
Selecione o intervalo de células onde o Excel deve pesquisar seu valor de pesquisa. Esse intervalo deve conter as possíveis duplicatas. - Defina a matriz de retorno
Selecione o intervalo de células do qual deseja extrair o resultado. Esse intervalo deve ter o mesmo tamanho que sua matriz de pesquisa. - Insira a fórmula básica
Na célula de resultado, digite =XLOOKUP(, clique na célula do valor de pesquisa, digite uma vírgula, selecione sua matriz de pesquisa, digite uma vírgula e selecione sua matriz de retorno. Feche a fórmula com um parêntese e pressione Enter.
Usando Argumentos Opcionais para Controle
Para controle preciso, você pode usar os argumentos opcionais. O argumento match_mode é essencial para garantir uma correspondência exata.
- Adicione o argumento se_não_encontrado
Após a matriz_retorno, digite uma vírgula e sua mensagem de erro personalizada entre aspas, como “Não Encontrado”. Isso torna a saída mais limpa se nenhuma correspondência existir. - Defina o match_mode como 0
Adicione outra vírgula após o texto se_não_encontrado e digite o número 0. Isso informa explicitamente ao XLOOKUP para encontrar uma correspondência exata. - Confirme o search_mode
Geralmente você pode omitir o argumento search_mode final. Se incluí-lo, use 1 para pesquisar do primeiro ao último ou -1 para pesquisar do último ao primeiro. O padrão é 1, que encontra a primeira correspondência.
Erros Comuns e Limitações a Evitar
A Fórmula Retorna Erro #N/D
Esse erro significa que o XLOOKUP não consegue encontrar seu valor de pesquisa. Primeiro, verifique se há erros de digitação ou espaços extras no valor de pesquisa ou nos dados dentro da matriz de pesquisa. Use a função TRIM para remover espaços. Certifique-se de que seu match_mode esteja definido como 0 para uma correspondência exata ou considere usar 1 para uma correspondência aproximada se estiver trabalhando com dados numéricos classificados.
A Fórmula Retorna o Valor Errado
Se o resultado estiver incorreto, sua matriz_pesquisa e matriz_retorno provavelmente estão desalinhadas. Verifique se ambos os intervalos selecionados começam na mesma linha e contêm o mesmo número de linhas. Um intervalo ser uma única célula enquanto o outro é uma coluna é um erro comum. Além disso, confirme se você está procurando na coluna correta para seu valor de pesquisa.
Precisa Encontrar a Última Correspondência
O XLOOKUP pode encontrar a última duplicata correspondente alterando o argumento search_mode. Use uma fórmula como =XLOOKUP(F2, A2:A100, B2:B100, , 0, -1). O -1 no final instrui o Excel a pesquisar de baixo para cima na lista, retornando a última correspondência encontrada.
XLOOKUP vs. VLOOKUP para Primeira Correspondência
| Item | XLOOKUP | VLOOKUP |
|---|---|---|
| Comportamento de pesquisa padrão | Retorna a primeira correspondência automaticamente | Retorna a primeira correspondência automaticamente |
| Flexibilidade de direção de pesquisa | Pode pesquisar do primeiro ao último ou do último ao primeiro | Só pode pesquisar de cima para baixo |
| Método de referência de coluna | Usa um intervalo de matriz de retorno separado | Requer um número de índice de coluna estático |
| Tratamento de valores não encontrados | Possui um argumento [se_não_encontrado] dedicado | Requer encapsulamento na função IFERROR |
| Localização da matriz de pesquisa | A matriz de pesquisa pode ser qualquer coluna, à esquerda ou à direita | O valor de pesquisa deve estar na primeira coluna da tabela |
Agora você pode usar XLOOKUP para extrair de forma limpa o primeiro valor correspondente de uma lista com duplicatas. Tente usar o argumento [se_não_encontrado] para lidar com dados ausentes de forma elegante em seus relatórios. Para cenários mais complexos, lembre-se de que você pode usar XLOOKUP para realizar uma pesquisa bidirecional aninhando-a dentro de outro XLOOKUP como argumento valor_procurado.