Como Usar a NOVA e AVANÇADA Função XLOOKUP no Excel (10 Exemplos)
O novo XLOOKUP do Excel é a função de pesquisa mais poderosa e fácil de usar que o Excel pode oferecer. Após muito esforço, a Microsoft finalmente lançou essa função para substituir VLOOKUP, HLOOKUP, INDEX+MATCH e outras funções de pesquisa.
Neste tutorial, mostraremos quais são as vantagens do XLOOKUP, como obtê-lo e aplicá-lo para resolver diferentes problemas de pesquisa.
Baixar Arquivo de Exemplo do XLOOKUP
Como Obter o XLOOKUP?
A função XLOOKUP está disponível apenas no Excel para Microsoft365, Excel2021 e versões posteriores, além do Excel para web. Se você estiver usando o Excel2019 ou anterior, considere atualizar para acessar o XLOOKUP.
Sintaxe
O XLOOKUP pesquisa um intervalo ou matriz e retorna o valor do primeiro resultado correspondente. A sintaxe é a seguinte:
Argumentos:
- Lookup_value (obrigatório): o valor que você está procurando. Ele pode estar em qualquer coluna do intervalo table_array.
- Lookup_array (obrigatório): a matriz ou intervalo onde você irá pesquisar o valor de pesquisa.
- Return_array (obrigatório): a matriz ou intervalo de onde você deseja obter o valor.
- If_not_found (opcional): o valor a ser retornado quando uma correspondência válida não for encontrada. Você pode personalizar o texto em [if_not_found] para indicar que não há correspondência.
Caso contrário, o valor retornado será #N/D por padrão. - Match_mode (opcional): aqui você pode especificar como comparar lookup_value com os valores em lookup_array.
- 0 (padrão) = Correspondência exata. Se não houver correspondência, retorna #N/D.
- -1 = Correspondência exata. Se não houver correspondência, retorna o próximo valor menor.
- 1 = Correspondência exata. Se não houver correspondência, retorna o próximo valor maior.
- 2 = Correspondência parcial. Use caracteres curingas como *, ? e ~ para realizar a correspondência com curingas.
- Search_mode (opcional): aqui você pode especificar a ordem de pesquisa a ser realizada.
- 1 (padrão) = Pesquisa o lookup_value do primeiro ao último item em lookup_array.
- -1 = Pesquisa o lookup_value do último ao primeiro item. Útil quando você precisa obter o último resultado correspondente em lookup_array.
- 2 = Realiza uma pesquisa binária que exige lookup_array classificado em ordem crescente. Se não estiver classificado, o resultado será inválido.
- -2 = Realiza uma pesquisa binária que exige lookup_array classificado em ordem decrescente. Se não estiver classificado, o resultado será inválido.
Para informações detalhadas sobre os argumentos, siga os passos abaixo:
1. Digite a sintaxe abaixo em uma célula vazia. Observe que você só precisa digitar um lado do parêntese.
2. Pressione Ctrl+A, então uma caixa de diálogo será exibida mostrando os Argumentos da Função. O outro lado do parêntese será completado automaticamente.
3. Expanda o painel de dados para visualizar todos os seis argumentos da função XLOOKUP.
 | >>> |  |
Exemplos
Tenho certeza de que você já dominou os princípios básicos do XLOOKUP. Vamos direto aos exemplos práticos do XLOOKUP.
Exemplo1: Correspondência exata
Realizar uma correspondência exata com XLOOKUP
Você já ficou frustrado por ter que especificar o modo de correspondência exata sempre que usava o VLOOKUP? Felizmente, esse problema não existe mais ao usar a incrível função XLOOKUP. Por padrão, o XLOOKUP realiza a correspondência exata.
Agora, suponha que você tenha uma lista de estoque de suprimentos de escritório e precise saber o preço unitário de um item, por exemplo, mouse. Siga os passos abaixo.
Digite a fórmula abaixo na célula F2 e pressione Enter para obter o resultado.
=XLOOKUP(E2,A2:A10,C2:C10)
Agora você sabe o preço unitário do Mouse com a fórmula avançada do XLOOKUP. Como o modo de correspondência já está definido como exato por padrão, não é necessário especificá-lo. Muito mais fácil e eficiente do que o VLOOKUP.
Apenas alguns cliques para obter a correspondência exata
Talvez você esteja usando uma versão mais antiga do Excel e não pretende atualizar para o Excel2021 ou Microsoft365 no momento. Nesse caso, recomendo um recurso prático - "Localizar dados em um intervalo". Com esse recurso, você pode obter o resultado sem fórmulas complicadas ou acesso ao XLOOKUP.
1. Clique na célula onde deseja exibir o resultado correspondente.
2. Vá até a guia "Kutools", clique em "Assistente de Fórmulas" e, em seguida, clique novamente em "Assistente de Fórmulas" na lista suspensa.
3. Na caixa de diálogo Assistente de Fórmulas, configure da seguinte forma:
- Selecione "Procurar" na seção "Tipo de Fórmula";
- Na seção "Selecione uma fórmula", escolha "Localizar dados em um intervalo";
- Na seção "Entrada de Argumento", siga os passos abaixo:
- Na caixa "Intervalo da tabela", selecione o intervalo de dados que contém o valor de pesquisa e o valor de resultado;
- Na caixa "Valor de pesquisa", selecione a célula ou intervalo do valor que está procurando. Observe que deve estar na primeira coluna do intervalo da tabela;
- Na caixa "Coluna", selecione a coluna de onde será retornado o valor correspondente.
4. Clique no botão OK para obter o resultado.
Kutools para Excel - Potencialize o Excel com mais de 300 ferramentas essenciais. Aproveite recursos de IA permanentemente gratuitos! Obtenha Agora
Exemplo2. Correspondência aproximada
Realizar uma correspondência aproximada com XLOOKUP
Para executar uma pesquisa aproximada, é necessário definir o modo de correspondência como1 ou -1 no quinto argumento. Quando não há correspondência exata, retorna o próximo valor maior ou menor.
Neste caso, você precisa saber as taxas de imposto de renda dos funcionários. À esquerda da planilha estão as faixas do Imposto de Renda Federal para2021. Como obter a taxa de imposto do funcionário na coluna E? Não se preocupe. Siga os passos abaixo:
1. Digite a fórmula abaixo na célula E2 e pressione Enter para obter o resultado.
Em seguida, altere a formatação do resultado retornado conforme necessário.
=XLOOKUP(D2,B2:B8,A2:A8,,1)
 | >>> |  |
√ Observação: O quarto argumento [If_not_found] é opcional, por isso foi omitido.
2. Agora você sabe a taxa de imposto da célula D2. Para obter os demais resultados, é necessário converter as referências das células do lookup_array e return_array para absolutas.
- Clique duas vezes na célula E2 para exibir a fórmula =XLOOKUP(D2,B2:B8,A2:A8,,1);
- Selecione o intervalo de pesquisa B2:B8 na fórmula, pressione F4 para obter $B$2:$B$8;
- Selecione o intervalo de retorno A2:A8 na fórmula, pressione F4 para obter $A$2:$A$8;
- Pressione Enter para obter o resultado da célula E2.
 | >>> |  |
3. Em seguida, arraste a alça de preenchimento para baixo para obter todos os resultados.
√ Observação:
- Ao pressionar a tecla F4 no teclado, você pode transformar a referência da célula em referência absoluta, adicionando cifrões antes da linha e da coluna.
- Após aplicar a referência absoluta ao intervalo de pesquisa e de retorno, a fórmula na célula E2 foi alterada para esta versão:
=XLOOKUP(D2,$B$2:$B$8,$A$2:$A$8,,1)
- Ao arrastar a alça de preenchimento a partir da célula E2, as fórmulas em cada célula da coluna E mudam apenas no argumento lookup_value.
Por exemplo, a fórmula em E13 agora ficou assim:
=XLOOKUP(D13,$B$2:$B$8,$A$2:$A$8,,1)
Exemplo3: Correspondência com curingas
Realizar uma correspondência com curingas usando XLOOKUP
Antes de explorarmos a função de correspondência com curingas do XLOOKUP, vamos entender o que são curingas.
No Microsoft Excel, curingas são caracteres especiais que podem substituir qualquer caractere. Eles são especialmente úteis quando você deseja realizar buscas por correspondência parcial.
Existem três tipos de curingas: asterisco (*), ponto de interrogação (?) e til (~).
- Asterisco (*) representa qualquer quantidade de caracteres no texto;
- Ponto de interrogação (?) representa qualquer caractere único no texto;
- Til (~) é usado para transformar os curingas (*, ? ~) em caracteres literais. Coloque o til (~) antes do curinga para isso;
Na maioria dos casos, ao usar a função de correspondência com curingas do XLOOKUP, utilizamos o caractere asterisco (*). Agora veja como funciona a correspondência com curingas.
Suponha que você tenha uma lista de Capitalização de Mercado das50 Maiores Empresas Americanas e queira saber o valor de mercado de algumas empresas, mas os nomes estão abreviados. Esse é o cenário perfeito para usar a correspondência com curingas. Siga o passo a passo:
√ Observação: Para realizar a correspondência com curingas, o mais importante é definir o quinto argumento [match_mode] como2.
1. Digite a fórmula abaixo na célula H3 e pressione Enter para obter o resultado.
=XLOOKUP("*"&G3&"*",B3:B52,D3:D52,,2)
 | >>> |  |
2. Agora você sabe o resultado da célula H3. Para obter os demais resultados, é necessário fixar o lookup_array e o return_array colocando o cursor na matriz e pressionando F4. Assim, a fórmula em H3 fica:
=XLOOKUP("*"&G3&"*",$B$3:$B$52,$D$3:$D$52,,2)
3. Arraste a alça de preenchimento para baixo para obter todos os resultados.
√ Observação:
- O lookup_value da fórmula na célula H3 é "*"&G3&"*". Concatenamos o curinga asterisco (*) com o valor de G3 usando o operador &.
- O quarto argumento [If_not_found] é opcional, por isso foi omitido.
Exemplo4: Procurar à esquerda
Pesquisar da direita para a esquerda usando XLOOKUP
Uma desvantagem do VLOOKUP é que ele só permite pesquisar valores à direita da coluna de pesquisa. Se tentar buscar valores à esquerda, obterá o erro #N/D. Não se preocupe. O XLOOKUP é a função ideal para resolver esse problema.
O XLOOKUP foi projetado para pesquisar valores tanto à esquerda quanto à direita da coluna de pesquisa. Ele não tem limites e atende às necessidades dos usuários do Excel. No exemplo abaixo, mostraremos como fazer isso.
Suponha que você tenha uma lista de países com códigos telefônicos e queira encontrar o nome do país a partir de um código conhecido.
Precisamos pesquisar na coluna C e retornar o valor da coluna A. Siga os passos abaixo:
1. Digite a fórmula abaixo na célula G2.
=XLOOKUP(F2,C2:C11,A2:A11)
2. Pressione Enter para obter o resultado.
√ Observação: A função de pesquisa à esquerda do XLOOKUP pode substituir o uso de Index e Match para buscar valores à esquerda.
Pesquisar valor da direita para a esquerda em poucos cliques
Para quem não quer memorizar fórmulas, recomendo um recurso útil - "Procurar da direita para a esquerda". Com esse recurso, você pode realizar a pesquisa da direita para a esquerda em poucos segundos.
1. Vá até a guia "Kutools" no Excel, encontre "Super PROC" e clique em "Procurar da direita para a esquerda" na lista suspensa.
2. Na caixa de diálogo "Procurar da direita para a esquerda", configure da seguinte forma:
- Na seção "Intervalo de valores a serem pesquisados e intervalo de saída", especifique o intervalo de pesquisa e o intervalo de saída;
- Na seção "Range de dados", insira o range de dados e, em seguida, especifique a "coluna chave" e a "coluna de retorno";
3. Clique no botão OK para obter o resultado.
Kutools para Excel - Potencialize o Excel com mais de 300 ferramentas essenciais. Aproveite recursos de IA permanentemente gratuitos! Obtenha Agora
Exemplo5: Pesquisa vertical ou horizontal
Realizar uma pesquisa vertical ou horizontal com XLOOKUP
Como usuários do Excel, vocês já devem conhecer as funções VLOOKUP e HLOOKUP. O VLOOKUP realiza buscas verticais em uma coluna e o HLOOKUP faz buscas horizontais em uma linha.
Agora o novo XLOOKUP combina ambas as funções, ou seja, você só precisa de uma sintaxe para realizar pesquisas verticais ou horizontais. Genial, não é mesmo?
No exemplo abaixo, vamos mostrar como usar apenas uma sintaxe do XLOOKUP para executar pesquisas verticais ou horizontais.
Para realizar uma pesquisa vertical, digite a fórmula abaixo na célula E2 e pressione Enter para obter o resultado.
=XLOOKUP(E1,A2:A13,B2:B13)
Para realizar uma pesquisa horizontal, digite a fórmula abaixo na célula P2 e pressione Enter para obter o resultado.
=XLOOKUP(P1,B1:M1,B2:M2)
Como você pode ver, a sintaxe é a mesma. A única diferença entre as duas fórmulas é que, na pesquisa vertical, você insere colunas, enquanto na pesquisa horizontal, insere linhas.
Exemplo6: Pesquisa bidirecional
Realizar uma pesquisa bidirecional com XLOOKUP
Você ainda usa as funções INDEX e MATCH para buscar um valor em uma tabela bidimensional? Experimente o XLOOKUP aprimorado para facilitar seu trabalho.
O XLOOKUP pode realizar uma dupla pesquisa, encontrando a interseção de dois valores. Ao aninhar um XLOOKUP dentro de outro, o XLOOKUP interno pode retornar uma linha ou coluna inteira, que será usada como return_array no XLOOKUP externo.
Suponha que você tenha uma lista de notas de alunos em diferentes disciplinas e queira saber a nota de Química do Kim.
Veja como usar o XLOOKUP para resolver isso de forma prática.
- Executamos o XLOOKUP “interno” para retornar os valores de uma coluna inteira. XLOOKUP(H2,B1:E1,B2:E10) pode obter o intervalo das notas de Química.
- Aninhamos o XLOOKUP “interno” dentro do XLOOKUP “externo”, usando o XLOOKUP interno como return_array na fórmula completa.
- Veja a fórmula final:
=XLOOKUP(H1,A2:A10,XLOOKUP(H2,B1:E1,B2:E10))
- Digite a fórmula acima na célula H3 e pressione Enter para obter o resultado.
Ou você pode fazer o contrário: usar o XLOOKUP “interno” para retornar os valores de uma linha inteira, ou seja, todas as notas do Kim. Depois, use o XLOOKUP “externo” para buscar a nota de Química entre todas as notas do Kim.
- Digite a fórmula abaixo na célula H4 e pressione Enter para obter o resultado.
=XLOOKUP(H2,B1:E1,XLOOKUP(H1,A2:A10,B2:E10))
A função de pesquisa bidirecional do XLOOKUP também é um ótimo exemplo de sua capacidade de pesquisa vertical e horizontal. Experimente!
Exemplo7: Personalizar mensagem de não encontrado
Personalizar mensagem de não encontrado usando XLOOKUP
Assim como outras funções de pesquisa, quando não encontra uma correspondência, a mensagem de erro #N/D será exibida. Isso pode confundir alguns usuários do Excel. Mas a boa notícia é que o tratamento de erros está disponível no quarto argumento da função XLOOKUP.
Com o argumento [if_not_found] integrado, você pode especificar uma mensagem personalizada para substituir o resultado #N/D. Digite o texto desejado no quarto argumento opcional e coloque-o entre aspas duplas ("").
Por exemplo, a cidade Denver não foi encontrada, então o XLOOKUP retorna a mensagem de erro #N/D. Mas, após personalizarmos o quarto argumento com o texto “Sem correspondência”, a fórmula exibirá “Sem correspondência” em vez da mensagem de erro.
Digite a fórmula abaixo na célula F3 e pressione Enter para obter o resultado.
=XLOOKUP(E2,A2:A11,C2:C11,"Sem correspondência")
Personalize o erro #N/D com um recurso prático
Para substituir rapidamente o erro #N/D pela sua mensagem personalizada, o Kutools para Excel é uma ferramenta perfeita para ajudar você no Excel. Com o recurso integrado Substituir0 ou #N/D por em branco ou valor específico, você pode definir a mensagem de não encontrado sem fórmulas complicadas ou acesso ao XLOOKUP.
1. Vá até a guia "Kutools" no Excel, encontre "Super PROC" e clique em "Substituir0 ou #N/D por em branco ou valor específico" na lista suspensa.
2. Na caixa de diálogo "Substituir0 ou #N/D por em branco ou valor específico", configure da seguinte forma:
- Na seção "Intervalo de valores a serem pesquisados e intervalo de saída", selecione o intervalo de pesquisa e o intervalo de saída;
- Em seguida, selecione a opção "Substituir0 ou #N/D por um valor específico" e insira o texto desejado;
- Na seção "Range de dados", selecione o range de dados e, em seguida, especifique a "coluna chave" e a "coluna de retorno".
3. Clique no botão OK para obter o resultado. A mensagem personalizada será exibida quando não houver correspondência.
Kutools para Excel - Potencialize o Excel com mais de 300 ferramentas essenciais. Aproveite recursos de IA permanentemente gratuitos! Obtenha Agora
Exemplo8: Vários valores
Retornar vários valores com XLOOKUP
Outra vantagem do XLOOKUP é a capacidade de retornar vários valores ao mesmo tempo para a mesma correspondência. Digite uma fórmula para obter o primeiro resultado e os demais valores serão preenchidos automaticamente nas células vizinhas.
No exemplo abaixo, você deseja obter todas as informações do aluno com ID “FG9940005”. O segredo é fornecer um intervalo como return_array na fórmula, em vez de uma única coluna ou linha. Neste caso, o intervalo de retorno é B2:D9, incluindo três colunas.
Digite a fórmula abaixo na célula G2 e pressione Enter para obter todos os resultados.
=XLOOKUP(F2,A2:A9,B2:D9)
Todas as células de resultado exibem a mesma fórmula. Você pode editar ou modificar a fórmula na primeira célula, mas nas demais células a fórmula não é editável. A barra de fórmulas fica cinza, indicando que não é possível alterá-la.
Em resumo, a função de múltiplos valores do XLOOKUP é uma melhoria útil em relação ao VLOOKUP. Você não precisa mais especificar o número de cada coluna separadamente para cada fórmula. Muito prático!
Exemplo9. Vários Critérios
Realizar pesquisa com múltiplos critérios usando XLOOKUP
Outro recurso incrível do XLOOKUP é a capacidade de pesquisar com múltiplos critérios. O segredo é concatenar os valores de pesquisa e os arrays de pesquisa com o operador "&" separadamente na fórmula. Veja o exemplo abaixo.
Precisamos saber o preço do vaso azul médio. Neste caso, três valores de pesquisa (critérios) são necessários para encontrar a correspondência. Digite a fórmula abaixo na célula I2 e pressione Enter para obter o resultado.
=XLOOKUP(F2&G2&H2,A2:A12&B2:B12&C2:C12,D2:D12)
√ Observação: O XLOOKUP pode processar arrays diretamente. Não é necessário confirmar a fórmula com Control + Shift + Enter.
Pesquisa de várias condições com um método rápido
Existe uma maneira ainda mais rápida e fácil de realizar uma pesquisa com múltiplos critérios do que o XLOOKUP no Excel? O Kutools para Excel oferece um recurso incrível - "Pesquisa de várias condições". Com esse recurso, você pode executar uma pesquisa com múltiplos critérios em apenas alguns cliques!
1. Vá até a guia "Kutools" no Excel, encontre "Super PROC" e clique em "Pesquisa de várias condições" na lista suspensa.
2. Na caixa de diálogo "Pesquisa de várias condições", siga os passos abaixo:
- Na seção "Intervalo de valores a serem pesquisados e intervalo de saída", selecione o intervalo de valores de pesquisa e o intervalo de saída;
- Na seção "Range de dados", siga as operações abaixo:
- Selecione as colunas chave correspondentes que contêm os valores de pesquisa uma a uma, mantendo pressionada a tecla Ctrl na caixa "Chave";
- Especifique a coluna que contém os valores retornados na caixa "Retornar".
3. Clique no botão OK para obter o resultado.
√ Observação:
- A seção Substituir valores não encontrados (#N/D) por um valor especificado é opcional na caixa de diálogo, você pode definir ou não.
- O número de colunas inseridas na caixa Coluna de chave deve ser igual ao número de colunas inseridas na caixa Valores de pesquisa, e a ordem dos critérios em ambas as caixas deve corresponder um a um.
Kutools para Excel - Potencialize o Excel com mais de 300 ferramentas essenciais. Aproveite recursos de IA permanentemente gratuitos! Obtenha Agora
Exemplo10. Encontrar Valor com a Última Correspondência
Obter o último resultado correspondente com XLOOKUP
Para encontrar o último valor correspondente no Excel, defina o sexto argumento para pesquisar em ordem reversa.
Por padrão, o modo de pesquisa do XLOOKUP é1, ou seja, pesquisa do primeiro ao último. Mas o XLOOKUP permite alterar a direção da pesquisa. Basta definir o modo de pesquisa no sexto argumento como -1 para pesquisar do último para o primeiro.
Veja o exemplo abaixo. Queremos saber a última venda da Emma no banco de dados.
Digite a fórmula abaixo na célula G2 e pressione Enter para obter o resultado.
=XLOOKUP(F2,B2:B11,D2:D11,,,-1)
√ Observação: O quarto e o quinto argumentos são opcionais e foram omitidos neste caso. Apenas o sexto argumento opcional foi definido como -1.
Encontre facilmente o último valor correspondente com uma ferramenta incrível
Caso você não tenha acesso ao XLOOKUP e também não queira memorizar fórmulas complicadas, pode usar o recurso "Procurar de baixo para cima" para resolver facilmente.
1. Vá até a guia "Kutools" no Excel, encontre "Super PROC" e clique em "Procurar de baixo para cima" na lista suspensa.
2. Na caixa de diálogo "Procurar de baixo para cima", configure da seguinte forma:
- Na seção "Intervalo de valores a serem pesquisados e intervalo de saída", selecione o intervalo de pesquisa e o intervalo de saída;
- Na seção "Range de dados", selecione o range de dados e, em seguida, especifique a "coluna chave" e a "coluna de retorno".
3. Clique no botão OK para obter o resultado.
√ Observação: A seção Substituir valores não encontrados (#N/D) por um valor especificado é opcional na caixa de diálogo, você pode definir ou não.
Kutools para Excel - Potencialize o Excel com mais de 300 ferramentas essenciais. Aproveite recursos de IA permanentemente gratuitos! Obtenha Agora
Baixar Arquivo de Exemplo do XLOOKUP
Artigos relacionados:
- Como Usar INDEX e MATCH Juntos no Excel?
- Como Aplicar Correspondência Fuzzy para Encontrar Resultados Aproximados no Excel?
- Como Usar Fórmula de Pesquisa Bidirecional no Excel?
- Como Fazer Vlookup com Múltiplos Critérios no Excel?
- Como Fazer Vlookup de Valores da Direita para a Esquerda no Excel?
Melhores Ferramentas de Produtividade para Office
Impulsione suas habilidades no Excel com Kutools para Excel e experimente uma eficiência incomparável. Kutools para Excel oferece mais de300 recursos avançados para aumentar a produtividade e economizar tempo. Clique aqui para acessar o recurso que você mais precisa...
Office Tab traz interface com abas para o Office e facilita muito seu trabalho
- Habilite edição e leitura por abas no Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Abra e crie múltiplos documentos em novas abas de uma mesma janela, em vez de em novas janelas.
- Aumente sua produtividade em50% e economize centenas de cliques todos os dias!
Todos os complementos Kutools. Um instalador
O pacote Kutools for Office reúne complementos para Excel, Word, Outlook & PowerPoint, além do Office Tab Pro, sendo ideal para equipes que trabalham em vários aplicativos do Office.
- Pacote tudo-em-um — complementos para Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Um instalador, uma licença — configuração em minutos (pronto para MSI)
- Trabalhe melhor em conjunto — produtividade otimizada entre os aplicativos do Office
- Avaliação completa por30 dias — sem registro e sem cartão de crédito
- Melhor custo-benefício — economize comparado à compra individual de add-ins