Como usar a função XLOOKUP no Excel

Qual é a função Xlookup no Excel ? Como usar o Xlookup no Excel ? Vamos descobrir com LuckyTemplates.com!

Se você usa o Excel regularmente, provavelmente está familiarizado com as funções de pesquisa. Se você não sabe, basta entendê-lo como uma função do Excel usada para pesquisar dados ou valores específicos em linhas ou colunas de uma planilha.

Esta função é especialmente útil quando você precisa processar muitos dados em uma planilha Excel. No Excel, você tem vários tipos de funções LOOKUP. XLOOKUP é um deles. Então, qual é a função XLOOKUP, qual é a fórmula XLOOKUP e como usá-la? Vamos descobrir com LuckyTemplates.com!

Coisas que você deve saber sobre a função XLOOKUP no Excel

• O que é XLOOKUP?
  • Como acessar a função XLOOKUP
  • Sintaxe da função XLOOKUP
• Vantagens e desvantagens da função XLOOKUP no Excel
  • Vantagens da função XLOOKUP
  • Desvantagens da função XLOOKUP
• Como usar a função XLOOKUP
  • Não há números de índice de coluna adicionais
  • Correspondência exata por padrão
  • XLOOKUP pode ser encontrado à esquerda
  • Use XLOOKUP para pesquisar intervalos
  • XLOOKUP substitui a função HLOOKUP
  • XLOOKUP pode ser visualizado de baixo para cima

Qual é a função XLOOKUP no Excel?

Em essência, XLOOKUP substitui funções antigas como HLOOKUP, VLOOKUP e LOOKUP. Ele suporta a pesquisa de dados vertical e horizontalmente.

Em outras palavras, a função XLOOKUP do Excel ajuda a encontrar rapidamente valores no conjunto de dados fornecido, tanto horizontal quanto verticalmente. Em seguida, ele retorna o valor correspondente de acordo com diferentes linhas e colunas.

Existem muitas maneiras de maximizar o uso da função XLOOKUP.

Como acessar a função XLOOKUP

Atualmente, o XLOOKUP está disponível apenas para usuários do programa Insiders. Qualquer pessoa que ingressar no programa Insider poderá acessar os recursos mais recentes do Excel assim que estiverem disponíveis. A Microsoft lançará em breve esta função para todos os usuários do Office 365. Infelizmente, o XLOOKUP não está disponível para usuários que executam as versões do Office 2010, 2013, 2016 e 2019.

Se você não usa o pacote Microsoft 365 em seu computador, pode ser necessário atualizar para o Microsoft 365 para obter acesso à funcionalidade XLOOKUP. E se estiver usando o Microsoft 365, você verá a opção habilitada no Excel. XLOOKUP também está disponível no Office 365 Online.

Sintaxe da função XLOOKUP

A sintaxe da função XLOOKUP é semelhante à sintaxe das funções VLOOKUP e HLOOKUP. Se você já os usou antes, achará mais conveniente usar o XLOOKUP. Aqui está a sintaxe da função XLOOKUP no Excel:

= XLOOKUP ( valor_procurado , array_procurado , array_retornado , [if_not_found], [modo_correspondência], [modo_pesquisa])

A função XLOOKUP suporta até 6 argumentos e aqui estão seus valores.

1. lookup_value (obrigatório): O valor que você deseja encontrar.

2. lookup_array (obrigatório): o array onde você deseja pesquisar o valor de pesquisa.

3. return_array (obrigatório): Array de onde você deseja buscar e retornar valores quando o valor de pesquisa for encontrado.

4. [if_not_found] (opcional): Quando nenhuma correspondência for encontrada, este é o valor retornado.

5. [match_mode] (opcional): Este argumento permite especificar o tipo de correspondência que você está procurando. Existem diferentes valores para especificá-lo:

• 0 - Procura uma correspondência exata e o valor deve corresponder exatamente ao valor em lookup_array. Este valor também é definido como padrão quando não é mencionado.
• -1 - Procura uma correspondência exata e, uma vez encontrada, retornará para o próximo valor menor.
• 1 - Procura uma correspondência exata e quando encontrada retorna o próximo valor maior.
• 2 - Realize a correspondência parcial usando curingas onde *, ? e ~ tem um significado especial.

6. [search_mode] (opcional): Usado para especificar o modo de pesquisa de XLOOKUP em lookup_array. Existem valores diferentes para especificar o mesmo:

• 1 - Faça uma busca começando pelo primeiro item. Este valor é definido como padrão quando nada é especificado.
• -1 - Executa uma busca reversa começando pelo último item.
• 2 - Realize uma busca binária em lookup_array onde os dados precisam ser ordenados em ordem crescente. Se os dados não forem classificados, podem produzir erros ou resultados errados.
• -2 - Execute uma pesquisa binária em lookup_array onde os dados precisam ser classificados em ordem decrescente. Se os dados não forem classificados, podem produzir erros ou resultados errados.

Vantagens e desvantagens da função XLOOKUP no Excel

XLOOKUP ainda é uma função vantajosa em comparação com VLOOKUP e INDEX/ATCH. Mas também tem suas desvantagens.

Vantagens da função XLOOKUP

• A mandíbula funciona tanto vertical quanto horizontalmente.
• Apenas 3 argumentos são necessários, em vez de 4 como em VLOOKUP e INDEX MATCH.
• Sempre use como padrão uma correspondência exata.
• Pesquisas parciais podem ser realizadas usando curingas.
• As pesquisas podem ser realizadas em ordem decrescente.
• Use uma função em vez de duas em INDEX MATCH.

Desvantagens da função XLOOKUP

• Argumentos opcionais podem parecer complicados para iniciantes.
• Pode ser mais demorado selecionar dois intervalos e ter muitas células na planilha.
• Retorna um erro ao procurar e retornar uma matriz que não tem o mesmo comprimento.
• Lembre-se dos intervalos de pesquisa e retorno.

Benefícios de usar a função XLOOKUP Excel

• XLOOKUP torna as fórmulas mais usadas no Excel mais simples e menos propensas a erros . Você só precisa escrever =XLOOKUP (dados a serem encontrados, lista, lista de resultados) e você obterá a resposta ou #N/A se o valor não for encontrado.
• Os resultados da pesquisa padrão são exatos . Uma das desvantagens do VLOOKUP é que você deve mencionar FALSE como último parâmetro para obter o resultado correto. XLOOKUP corrige isso executando uma correspondência exata por padrão. Você pode usar o parâmetro do modo de correspondência para alterar o comportamento da pesquisa, se desejar.
• O quarto parâmetro suporta o caso em que o valor não foi encontrado . Na maioria das situações de negócios, você será forçado a "envolver" a fórmula de pesquisa com IFERROR ou IFNA para eliminar erros. XLOOKUP fornece um quarto parâmetro para que você possa saber o resultado padrão desejado caso o valor não seja encontrado.
• XLOOKUP fornece parâmetros de seleção para encontrar situações especiais . Você pode pesquisar de cima ou de baixo, por caracteres especiais e opções mais rápidas para encontrar dados filtrados.

Como usar a função XLOOKUP

Dê uma olhada no exemplo abaixo para entender como funciona o XLOOKUP. Neste exemplo, precisamos retornar a parte da coluna F para cada ID na coluna A.

Este é um exemplo que busca resultados exatos, mas a função XLOOKUP requer apenas três tipos de informações.

A imagem abaixo mostra XLOOKUP com 5 argumentos, mas os três primeiros argumentos são necessários para resultados corretos. Então vamos nos concentrar neles:

• Valor_procurado : valor de pesquisa
• Lookup_array : intervalo de pesquisa
• Return_array : intervalo contendo o valor a ser retornado

Neste exemplo usaremos a fórmula:

=XLOOKUP(A2,$E$2:$E$8,$F$2:$F$8)

Agora vamos explorar algumas das vantagens do XLOOKUP sobre o VLOOKUP.

Não há números de índice de coluna adicionais

O terceiro argumento de VLOOKUP é especificar o número de colunas de informações a serem retornadas do intervalo da tabela. Isso não é mais um problema com XLOOKUP porque a função permite ao usuário selecionar o intervalo de retorno. (coluna F neste exemplo).

E não se esqueça, XLOOKUP pode ver os dados restantes da célula selecionada, ao contrário de VLOOKUP.

Você também não encontrará erros de fórmula ao adicionar novas colunas. Caso isso aconteça na planilha, o intervalo de retorno será ajustado automaticamente.

Correspondência exata por padrão

Ao usar VLOOKUP, o usuário deve especificar uma correspondência exata, se desejar. Mas com XLOOKUP o padrão é uma correspondência exata. Isso reduz o quarto argumento e garante menos erros para novos usuários. Resumindo, XLOOKUP requer menos perguntas que VLOOKUP e é fácil de usar.

XLOOKUP pode ser encontrado à esquerda

A capacidade de selecionar intervalos de pesquisa oferece ao XLOOKUP mais flexibilidade do que ao VLOOKUP. Com XLOOKUP, a ordem das colunas na tabela não importa.

VLOOKUP é limitado pesquisando a coluna mais à esquerda da tabela e retornando de um número especificado de colunas à direita.

No exemplo abaixo, precisamos buscar o ID (coluna E) e retornar o nome da pessoa (coluna D).

Utilize a fórmula abaixo:

=XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8)

Use XLOOKUP para pesquisar intervalos

Embora não seja tão comum quanto a correspondência exata, você pode usar uma fórmula de pesquisa para encontrar um valor dentro de um intervalo. Por exemplo, queremos devolver um desconto dependendo do valor gasto.

Desta vez não estamos buscando um valor específico e precisamos saber em qual faixa os valores da coluna B se enquadram na coluna E para determinar o desconto.

XLOOKUP tem um quarto argumento opcional (lembre-se, o padrão é correspondência absoluta) chamado match_mode.

Você pode ver que XLOOKUP pode encontrar correspondências aproximadas melhor que VLOOKUP.

Existe uma opção para encontrar a correspondência mais próxima menor que (-1) ou a correspondência mais próxima maior que (1) o valor de pesquisa. Também existe a opção de usar curingas (2), como ? ou *. Esta configuração não está habilitada por padrão como acontece com VLOOKUP.

A fórmula neste exemplo retorna o valor mais próximo menor que o valor de pesquisa se nenhuma correspondência exata for encontrada:

=XLOOKUP(B2,$E$3:$E$7,$F$3:$F$7,-1)

Porém houve um erro na célula C7, o desconto de 0% deveria ter sido devolvido pois gastou apenas 64, que não atendeu aos critérios para receber o desconto.

Outra vantagem da função XLOOKUP é que ela não requer um intervalo de pesquisa em ordem crescente como VLOOKUP.

Insira uma nova linha na parte inferior da tabela de pesquisa e abra a fórmula. Expanda o escopo de uso clicando e arrastando os cantos.

A fórmula corrige imediatamente o erro. Não há problema em ter 0 no final da tabela de intervalos.

XLOOKUP substitui a função HLOOKUP

Conforme mencionado, a função XLOOKUP também pode substituir a função HLOOKUP. Uma função pode substituir duas funções, o que poderia ser melhor?

A função HLOOKUP procura horizontalmente, usada para pesquisar por linha.

Esta função não é tão famosa quanto VLOOKUP, mas é útil nos casos em que o título está na coluna A e os dados estão nas linhas 4 e 5 como no exemplo abaixo.

XLOOKUP pode pesquisar em ambas as direções, nas colunas e ao longo das linhas.

Neste exemplo, uma fórmula é usada para retornar o valor de vendas associado ao nome na coluna A2. Ele procura o nome na linha 4 e retorna o valor da linha 5:

=XLOOKUP(A2,B4:E4,B5:E5)

XLOOKUP pode ser visualizado de baixo para cima

Normalmente você precisa olhar de cima para baixo em uma lista para encontrar a primeira ocorrência de um valor. XLOOKUP possui um quinto argumento denominado search_mode. Este argumento permite redirecionar a pesquisa para começar do final de uma lista para encontrar a última presença de um valor.

No exemplo abaixo, precisamos encontrar o nível de estoque de cada produto na coluna A.

A tabela de pesquisa é organizada em ordem de data. Queremos retornar o nível de estoque da última verificação (última ocorrência do ID do Produto).

O quinto argumento da função XLOOKUP oferece quatro opções. Aqui usaremos a opção Pesquisar do último ao primeiro .

Fórmula usada neste exemplo:

=XLOOKUP(A2,$E$2:$E$9,$F$2:$F$9,,-1)

Nesta fórmula, o quarto argumento é omitido. É opcional e queremos que o padrão seja uma correspondência exata.

A função XLOOKUP é a sucessora esperada das funções VLOOKUP e HLOOKUP. Uma série de exemplos foram usados ​​neste artigo para demonstrar as vantagens da função XLOOKUP.

XLOOKUP é uma grande melhoria quando se trata de funções de pesquisa. A única limitação da função XLOOKUP é que ela não é compatível com versões anteriores. Se você compartilhar planilhas com versões mais antigas do Microsoft Office Suite, poderá receber erros.

No entanto, você pode acessar o XLOOKUP no Office 365 Online. XLOOKUP ainda está em desenvolvimento, então levará algum tempo para que os usuários do Excel o adotem.

Desejo-lhe sucesso!

Diferença entre XLOOKUP e VLOOKUP

	PROCV	XLOOKUP
Correspondência exata padrão	N	S
Retorna o valor à direita da pesquisa	S	S
Retorna o valor à esquerda da pesquisa	N	S
Procure por linha	N	S
Retorna mais de um valor	N	S
Precisa classificar a coluna de pesquisa	Apenas correspondências aproximadas	Somente pesquisa binária
Pesquise de cima para baixo	S	S
Pesquise de baixo para cima	N	S
Pesquisa binária	N	S
Personalize a mensagem se o valor de pesquisa não for encontrado	N	S
Pesquisar por curinga	S	S
Pesquisa exata	S	S
A pesquisa aproximada retorna o próximo valor menor	S	S
A pesquisa aproximada retorna o próximo valor maior	N	S
Um valor incorreto pode ser retornado se os valores de pesquisa não estiverem classificados	Y - aproximado	N - aproximado, Y - binário
Adicionar uma nova coluna para encontrar a tabela pode quebrar a fórmula	S	N

Por que o XLOOKUP é melhor que o VLOOKUP?

Embora VLOOKUP tenha seus próprios usos, ele possui recursos mais limitados que o XLOOKUP. Isso torna o XLOOKUP a escolha mais popular quando é necessário processar grandes quantidades de dados. Como resultado, ele funciona mais rápido e apresenta menos erros.

• XLOOKUP não limita o intervalo de dados : XLOOKUP é mais flexível que VLOOKUP. VLOOKUP pode procurar valores localizados apenas na coluna mais à esquerda de uma tabela e retornar valores da coluna correspondente à direita. Enquanto isso, XLOOKUP requer apenas etapas mais simples e pode retornar valores para qualquer coluna em qualquer direção.
• O resultado padrão de XLOOKUP é o valor mais preciso .
• Por padrão, a função VLOOKUP no Excel sempre encontra resultados aproximados . No entanto, se precisar de resultados exatos, você deve inserir FALSE no argumento range_lookup.
• A função XLOOKUP procura um valor de pesquisa em um intervalo específico e sempre assume o valor exato como padrão. Se não conseguir encontrar um, você pode editar o argumento para encontrar uma correspondência aproximada que seja menor ou maior que o valor de pesquisa.

Outras razões:

• XLOOKUP pode lidar com a inserção ou exclusão de colunas.
• XLOOKUP implementa pesquisas verticais e horizontais.
• XLOOKUP fornece correspondências aproximadas mais inteligentes.
• XLOOKUP precisa de menos referências de células.