M365Con 2027 – conferência online gratuita · 11–22 Jan 2027 · 120 sessions→ m365con.net

Blog · Mirko Peters

XLOOKUP no Excel: sintaxe, exemplos e por que ele supera o PROCV

By · · Updated

XLOOKUP no Excel explicado: a sintaxe, cada argumento, exemplos de correspondência exata, curinga e última ocorrência, várias colunas e as diferenças em relação ao PROCV.

Automatically translated with AI from the original post. Read the English original

O XLOOKUP no Excel encontra um valor em um intervalo e retorna o item correspondente de outro intervalo: =XLOOKUP(valor_pesquisado, matriz_pesquisa, matriz_retorno). Digite o ID de um funcionário, aponte para a coluna de IDs, aponte para a coluna de nomes e você obtém o nome. Este artigo é para usuários do Excel que ainda escrevem PROCV por hábito ou que acabaram de ver o XLOOKUP no arquivo de um colega. Você verá a sintaxe, os seis argumentos, exemplos práticos dos casos que surgem no trabalho, a comparação com o PROCV e uma lista dos erros que você provavelmente vai encontrar.

Pontos-chave

  • O XLOOKUP recebe um valor pesquisado, um intervalo de pesquisa e um intervalo de retorno: =XLOOKUP(valor_pesquisado, matriz_pesquisa, matriz_retorno). Ele encontra a primeira correspondência e retorna o item do intervalo de retorno.
  • Ele consegue buscar à esquerda da coluna de pesquisa e tem correspondência exata como padrão, duas coisas que o PROCV não faz ou não faz por padrão.
  • O quarto argumento, if_not_found, substitui o erro #N/D por um texto que você escolhe. Sem ele, uma pesquisa sem correspondência retorna #N/D.
  • O match_mode 2 ativa os curingas, -1 e 1 encontram o item imediatamente menor ou maior, e search_mode -1 pesquisa do último item para o primeiro.
  • A página da Microsoft lista o XLOOKUP para Microsoft 365, 2021 e 2024, e as observações sobre Excel 2016 e 2019 são contraditórias, então teste os arquivos compartilhados antes de depender dele.

A sintaxe e as opções abaixo seguem a página da Microsoft sobre a função XLOOKUP. Quando um padrão não está nessa página, o texto informa isso. É uma função do Excel e é a que a própria Microsoft sugere como melhor opção quando você abre a página de ajuda do PROCV.

A sintaxe e os seis argumentos

A fórmula completa fica assim:

=XLOOKUP(valor_pesquisado, matriz_pesquisa, matriz_retorno, [if_not_found], [match_mode], [search_mode])

Os três primeiros argumentos são obrigatórios. Os três últimos são opcionais.

  • lookup_value é o valor que você procura, por exemplo um ID na célula D2. Se você o omitir, o XLOOKUP retorna as células em branco que encontrar na matriz de pesquisa.
  • lookup_array é o intervalo ou matriz a pesquisar, como A2:A100.
  • return_array é o intervalo ou matriz de onde o valor será retornado, como B2:B100.
  • if_not_found é o texto exibido quando não há correspondência válida. Se você omiti-lo, o resultado será #N/D.
  • match_mode define como a correspondência funciona. 0 é a correspondência exata e o padrão. -1 significa correspondência exata ou, se não houver, o item imediatamente menor. 1 significa correspondência exata ou o item imediatamente maior. 2 é a correspondência com curingas, em que *, ? e ~ têm significado especial.
  • search_mode define a direção da pesquisa. 1 pesquisa do primeiro item para o último e é o padrão. -1 pesquisa do último para o primeiro. 2 e -2 são pesquisas binárias em dados ordenados em ordem crescente ou decrescente. A Microsoft alerta que dados não ordenados retornam resultados inválidos nos modos binários.

Mantenha o array de pesquisa e o array de retorno com o mesmo tamanho. Se o array de pesquisa cobrir as linhas 2 a 100, o array de retorno também deve cobrir as linhas 2 a 100. A página da Microsoft não descreve isso como regra, mas intervalos que não coincidem são o motivo mais comum para uma pesquisa retornar a linha errada ou um erro.

Pesquisas básicas e o argumento de não encontrado

Digamos que os IDs de funcionários estejam em A2:A100 e os nomes em B2:B100. Você digita o ID desejado em D2.

  1. Clique na célula onde o resultado deve aparecer, por exemplo E2.
  2. Digite =XLOOKUP(D2,A2:A100,B2:B100).
  3. Pressione Enter.

Você obtém o nome correspondente ao ID. O XLOOKUP retorna o item da primeira correspondência que encontrar. Se o ID não existir, você verá #N/D.

Para exibir algo mais amigável, adicione o quarto argumento:

=XLOOKUP(D2,A2:A100,B2:B100,"Registro não encontrado")

Agora, um ID não listado exibe "Registro não encontrado". Use isso em planilhas que outras pessoas leem. Uma ressalva: uma mensagem assim pode esconder um problema real. Se todas as linhas exibirem "Registro não encontrado", o problema está nos intervalos, não nos dados, então teste primeiro com um ID conhecido.

Pesquisando para a esquerda

O PROCV não consegue retornar um valor de uma coluna à esquerda da coluna de pesquisa. O XLOOKUP consegue, porque os intervalos de pesquisa e de retorno são independentes. Se o nome estiver na coluna C e o ID que você quer de volta estiver na coluna A:

=XLOOKUP(F2,C2:C100,A2:A100)

A página da Microsoft afirma isso diretamente: a coluna de pesquisa pode estar em qualquer lado da coluna de retorno.

Modos de correspondência: curingas e valores mais próximos

Correspondências com curingas

Defina match_mode como 2 para usar curingas. O asterisco representa qualquer número de caracteres, e o ponto de interrogação representa exatamente um. O til permite pesquisar um asterisco ou um ponto de interrogação literais.

=XLOOKUP("Jan*",B2:B100,A2:A100,"Não encontrado",2)

Isso retorna a primeira entrada que começa com "Jan". Se você esquecer o 2, o Excel procura o texto literal "Jan*" e retorna sua mensagem de não encontrado. Esse é o erro mais comum com curingas. Mantenha os curingas o mais específicos possível, porque o XLOOKUP retorna apenas a primeira correspondência, e um padrão muito amplo pode encontrar algo que você não pretendia.

Próximo item menor ou maior

Os modos -1 e 1 são para escalas: alíquotas de impostos, faixas de comissão, faixas de preço. Coloque os limites em A2:A6 e as alíquotas em B2:B6, e pesquise uma renda em E2:

=XLOOKUP(E2,$A$2:$A$6,$B$2:$B$6,,-1)

Com -1, o Excel retorna a correspondência exata ou, se não houver, o limite imediatamente menor. O espaço vazio antes do -1 ignora o argumento de não encontrado. O exemplo da própria Microsoft para a correspondência aproximada usa 1 para retornar a alíquota de imposto do valor de renda imediatamente maior, então escolha a direção que se adapta à sua tabela. Diferentemente do PROCV aproximado tradicional, você não precisa ordenar os limites para esses modos. Os modos de pesquisa binária são os que exigem dados ordenados.

Pesquisar de baixo para cima: a última correspondência

Quando um valor aparece várias vezes, o XLOOKUP retorna a primeira ocorrência que encontra. Para obter a última, pesquise a partir do fim definindo search_mode como -1:

=XLOOKUP(D2,A2:A100,C2:C100,"Não encontrado",0,-1)

Se a planilha listar pedidos em ordem de data, isso retorna a entrada mais recente de um cliente. O 0 mantém a correspondência exata. Esse é o padrão para perguntas como "preço mais recente", "último contato" e outras que eram complicadas com o PROCV.

Retornar várias colunas de uma vez

A página da Microsoft observa que, diferentemente do PROCV, o XLOOKUP pode retornar várias colunas como um único array. Amplie o intervalo de retorno:

=XLOOKUP(D2,A2:A100,B2:D100)

A fórmula coloca a correspondência da coluna B na célula e transborda os valores das colunas C e D para as células à direita. Deixe espaço para eles. Se as células à direita já tiverem dados, o Excel mostra um erro de transbordamento em vez de sobrescrevê-los. O exemplo da Microsoft retorna o nome e o departamento de um funcionário em uma única fórmula.

Pesquisas bidirecionais

A Microsoft mostra como aninhar duas funções XLOOKUP para encontrar um valor na interseção de uma linha e uma coluna. O padrão, segundo a página, é:

=XLOOKUP(D2,$B6:$B17,XLOOKUP($C3,$C5:$G5,$C6:$G17))

A função interna escolhe a coluna que corresponde ao cabeçalho em C3. A função externa então procura D2 nos rótulos de linha e retorna o valor dessa coluna. A Microsoft compara isso a uma combinação de INDEX e MATCH. Se você tem um relatório com produtos na vertical e meses na horizontal, essa é a substituta de uma fórmula bem mais complicada.

Vários critérios

O XLOOKUP recebe um único valor de pesquisa, e a página da Microsoft não tem exemplo com vários critérios. Um padrão comum é deixar o Excel testar duas condições ao mesmo tempo. Suponha que a categoria esteja na coluna A, o status na coluna B e o preço na coluna C. Para encontrar o preço da categoria em F2 e do status em G2:

=XLOOKUP(1,(A2:A100=F2)*(B2:B100=G2),C2:C100,"Not found")

Cada comparação retorna VERDADEIRO ou FALSO. Multiplicá-las dá 1 apenas nas linhas em que as duas são verdadeiras, e o XLOOKUP procura o primeiro 1. É um padrão que funciona nas versões atuais do Microsoft 365, mas não está na página de funções da Microsoft, então teste em uma pequena amostra. Uma coluna auxiliar que une os dois campos é a alternativa mais antiga. Coloque um separador entre os valores, por exemplo =A2&"|"&B2, e procure o mesmo texto unido.

XLOOKUP e VLOOKUP lado a lado

A página do VLOOKUP da Microsoft apresenta a sintaxe como VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) e diz que o valor de pesquisa precisa estar na primeira coluna do intervalo. Se você omitir range_lookup, o VLOOKUP faz uma correspondência aproximada, que pressupõe que a primeira coluna esteja ordenada. Defina como FALSO para uma correspondência exata. A mesma página sugere experimentar o XLOOKUP e diz que ele funciona em qualquer direção e retorna correspondências exatas por padrão.

A mesma tarefa nas duas funções, com IDs na coluna A e nomes na coluna B:

  • =VLOOKUP(D2,A2:B100,2,FALSE)
  • =XLOOKUP(D2,A2:A100,B2:B100)

As diferenças que importam no uso diário:

  • O VLOOKUP se refere ao resultado pelo número da coluna, então inserir uma coluna na tabela muda o que o número significa. O XLOOKUP se refere diretamente ao intervalo de retorno.
  • O VLOOKUP não consegue pesquisar à esquerda. O XLOOKUP consegue.
  • O VLOOKUP usa correspondência aproximada por padrão. O XLOOKUP usa correspondência exata.
  • O XLOOKUP tem um argumento para não encontrado e a direção da pesquisa embutida.

Você não precisa reescrever pastas de trabalho antigas. Se um VLOOKUP funciona, deixe como está. Use o XLOOKUP em fórmulas novas e em pesquisas que exijam pesquisa à esquerda, última correspondência ou várias colunas de retorno.

Quais versões do Excel oferecem suporte ao XLOOKUP

Aqui é preciso ter cuidado. A página da Microsoft lista o XLOOKUP para o Excel para Microsoft 365 no Windows e no Mac, Excel 2024, Excel 2021, e os aplicativos para iPad, iPhone e Android. Uma nota na página diz que ele não está disponível no Excel 2016 ou 2019, mas pode aparecer em pastas de trabalho criadas em versões mais recentes, e a mesma página também cita 2016 e 2019 na linha de versões aplicáveis. Há uma contradição, então não trate o Excel 2016 e 2019 como seguros. O Excel para a Web também não aparece nessa página.

Se você enviar uma pasta de trabalho para alguém com uma versão mais antiga, a fórmula pode mostrar um erro onde deveria mostrar um valor. Pergunte qual versão seus destinatários usam e, para arquivos que vão para muitas pessoas, teste na versão mais antiga em uso. Se não puder testar, use uma pesquisa clássica nesse arquivo.

Erros e equívocos comuns

  • Esquecer o match_mode 2 ao usar curingas. A pesquisa então procura os caracteres literais.
  • Usar um search_mode binário (2 ou -2) em dados não ordenados. A Microsoft diz que os resultados são inválidos.
  • Intervalos de pesquisa e de retorno com tamanhos diferentes. Deixe-os com o mesmo comprimento.
  • Espaços ocultos e números armazenados como texto. "100" e 100 são diferentes, então a pesquisa falha e você vê #N/D. Limpe os dados com TRIM ou converta os números.
  • Ocultar problemas com uma mensagem amigável de não encontrado. Teste um valor conhecido antes de confiar no resultado.
  • Apontar a matriz de retorno para células onde o resultado precisa transbordar, e deixá-las ocupadas.
  • Referências de coluna inteira em planilhas muito grandes. Use um intervalo delimitado ou uma Tabela do Excel, para que o Excel percorra menos células.

Tópicos relacionados para explorar

  • XLOOKUP versus INDEX e MATCH. Ambos fazem pesquisa à esquerda, e a dupla INDEX e MATCH também funciona em versões mais antigas do Excel. Leia sobre o assunto se você compartilha arquivos com pessoas que usam o Excel 2016 ou 2019.
  • Vários resultados. O XLOOKUP retorna apenas a primeira correspondência. Para obter todas as correspondências, use a função FILTER.
  • XLOOKUP com tabelas do Excel. As referências estruturadas fazem os intervalos acompanharem o crescimento dos dados e deixam as fórmulas mais legíveis.
  • Dados do Power Query. Depois de carregar os dados em uma tabela, o XLOOKUP pode pesquisá-los como qualquer outra tabela. Atualize a consulta primeiro e confira os resultados depois.
  • Excel para a Web. A página da Microsoft sobre o XLOOKUP não a menciona, então verifique no seu próprio ambiente se a função funciona para seus colegas antes de compartilhar um arquivo.

Próximo passo

Pegue um PROCV da sua pasta de trabalho atual e reescreva-o como XLOOKUP. Adicione uma mensagem para quando não houver correspondência e compare os dois resultados com alguns IDs que você conhece. Se ambos coincidirem, você terá um padrão que pode reutilizar em todos os novos XLOOKUPs no Excel.

Fontes

Entities in this article

Microsoft Excel Excel for the web

Related articles

RSS