Como usar a função XLOOKUP no Microsoft Excel

O novo XLOOKUP do Excel substituirá o VLOOKUP, fornecendo um substituto poderoso para uma das funções mais populares do Excel. Esta nova função resolve algumas das limitações do VLOOKUP e possui funcionalidades extras. Aqui está o que você precisa saber.

O que é XLOOKUP?

A nova função XLOOKUP tem soluções para algumas das maiores limitações do VLOOKUP. Além disso, também substitui HLOOKUP. Por exemplo, XLOOKUP pode olhar para a esquerda, o padrão é uma correspondência exata e permite que você especifique um intervalo de células em vez de um número de coluna. O VLOOKUP não é tão fácil de usar ou tão versátil. Mostraremos como tudo funciona.

No momento, o XLOOKUP está disponível apenas para usuários do programa Insiders. Qualquer pessoa pode participar do programa Insiders para acessar os recursos mais recentes do Excel assim que estiverem disponíveis. A Microsoft começará em breve a implementá-lo para todos os usuários do Office 365.

Como usar a função XLOOKUP

Vamos mergulhar direto com um exemplo de XLOOKUP em ação. Pegue os dados de exemplo abaixo. Queremos retornar o departamento da coluna F para cada ID na coluna A.

Este é um exemplo clássico de pesquisa de correspondência exata. A função XLOOKUP requer apenas três informações.

A imagem abaixo mostra XLOOKUP com seis argumentos, mas apenas os três primeiros são necessários para uma correspondência exata. Então vamos focar neles:

• Lookup_value: O que você está procurando.
• Lookup_array: Onde procurar.
• Return_array: o intervalo que contém o valor a ser retornado.

A seguinte fórmula funcionará para este exemplo: =XLOOKUP(A2,$E$2:$E$8,$F$2:$F$8)

Vamos agora explorar algumas vantagens que o XLOOKUP tem sobre o VLOOKUP aqui.

Não há mais número de índice de coluna

O terceiro argumento infame de VLOOKUP era especificar o número da coluna das informações a serem retornadas de uma matriz de tabela. Isso não é mais um problema porque o XLOOKUP permite que você selecione o intervalo do qual retornar (coluna F neste exemplo).

E não se esqueça, o XLOOKUP pode visualizar os dados à esquerda da célula selecionada, ao contrário do VLOOKUP. Mais sobre isso abaixo.

Você também não tem mais o problema de uma fórmula quebrada quando novas colunas são inseridas. Se isso acontecesse em sua planilha, o intervalo de retorno seria ajustado automaticamente.

A correspondência exata é o padrão

Sempre foi confuso ao aprender VLOOKUP por que você precisava especificar uma correspondência exata.

Felizmente, o padrão XLOOKUP é uma correspondência exata - o motivo muito mais comum para usar uma fórmula de pesquisa). Isso reduz a necessidade de responder a esse quinto argumento e garante menos erros por usuários novos na fórmula.

Resumindo, o XLOOKUP faz menos perguntas do que o VLOOKUP, é mais fácil de usar e também mais durável.

XLOOKUP pode olhar para a esquerda

Ser capaz de selecionar um intervalo de pesquisa torna o XLOOKUP mais versátil do que o VLOOKUP. Com XLOOKUP, a ordem das colunas da tabela não importa.

PROCV foi restringido pesquisando a coluna mais à esquerda de uma tabela e, em seguida, retornando de um número especificado de colunas à direita.

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

A seguinte fórmula pode fazer isso: =XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8)

O que fazer se não for encontrado

Os usuários de funções de pesquisa estão muito familiarizados com a mensagem de erro #N/A que os recebe quando a função VLOOKUP ou MATCH não consegue encontrar o que precisa. E muitas vezes há uma razão lógica para isso.

Portanto, os usuários pesquisam rapidamente como ocultar esse erro porque não é correto ou útil. E, claro, existem maneiras de fazer isso.

O XLOOKUP vem com seu próprio argumento “se não encontrado” embutido para lidar com esses erros. Vamos vê-lo em ação com o exemplo anterior, mas com um ID digitado incorretamente.

A fórmula a seguir exibirá o texto “ID incorreto” em vez da mensagem de erro: =XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8,"Incorrect ID")

Usando XLOOKUP para uma pesquisa de intervalo

Embora não seja tão comum quanto a correspondência exata, um uso muito eficaz de uma fórmula de pesquisa é procurar um valor em intervalos. Tome o seguinte exemplo. Queremos devolver o desconto dependendo do valor gasto.

Desta vez não estamos procurando um valor específico. Precisamos saber onde os valores da coluna B estão dentro dos intervalos da coluna E. Isso determinará o desconto obtido.

XLOOKUP tem um quinto argumento opcional (lembre-se, o padrão é a correspondência exata) chamado match mode.

Você pode ver que o XLOOKUP tem mais recursos com correspondências aproximadas do que o VLOOKUP.

Existe a opção de encontrar a correspondência mais próxima menor que (-1) ou mais próxima maior que (1) o valor procurado. Há também uma opção para usar caracteres curinga (2), como o ? ou o *. Essa configuração não está ativada por padrão, como era com PROCV.

A fórmula neste exemplo retorna o valor mais próximo do valor procurado se uma correspondência exata não for encontrada: =XLOOKUP(B2,$E$3:$E$7,$F$3:$F$7,,-1)

No entanto, há um erro na célula C7 onde o erro #N/A é retornado (o argumento 'se não encontrado' não foi usado). Isso deveria ter retornado um desconto de 0% porque gastar 64 não atinge os critérios para nenhum desconto.

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

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

A fórmula corrige imediatamente o erro. Não é um problema ter o “0” na parte inferior do intervalo.

Pessoalmente, eu ainda classificaria a tabela pela coluna de pesquisa. Ter “0” na parte inferior me deixaria louco. Mas o fato de que a fórmula não quebrou é brilhante.

XLOOKUP também substitui a função HLOOKUP

Como mencionado, a função XLOOKUP também está aqui para substituir HLOOKUP. Uma função para substituir duas. Excelente!

A função HLOOKUP é a pesquisa horizontal, usada para pesquisar ao longo das linhas.

Não tão conhecido quanto seu irmão VLOOKUP, mas útil para exemplos como abaixo, onde os cabeçalhos estão na coluna A e os dados estão nas linhas 4 e 5.

O XLOOKUP pode olhar em ambas as direções – colunas para baixo e também ao longo das linhas. Não precisamos mais de duas funções diferentes.

Neste exemplo, a fórmula é usada para retornar o valor de vendas relacionado ao nome na célula A2. Ele procura na linha 4 o nome e retorna o valor da linha 5: =XLOOKUP(A2,B4:E4,B5:E5)

XLOOKUP pode olhar de baixo para cima

Normalmente, você precisa procurar uma lista para encontrar a primeira (muitas vezes apenas) ocorrência de um valor. XLOOKUP tem um sexto argumento chamado modo de pesquisa. Isso nos permite alternar a pesquisa para iniciar na parte inferior e procurar uma lista para encontrar a última ocorrência de um valor.

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

A tabela de pesquisa está em ordem de data e há várias verificações de estoque por produto. Queremos retornar o nível de estoque da última vez que foi verificado (última ocorrência do ID do produto).

O sexto argumento da função XLOOKUP oferece quatro opções. Estamos interessados ​​em usar a opção “Pesquisar do último para o primeiro”.

A fórmula completa é mostrada aqui: =XLOOKUP(A2,$E$2:$E$9,$F$2:$F$9,,,-1)

Nesta fórmula, o quarto e quinto argumento foram ignorados. É opcional e queríamos o padrão de uma correspondência exata.

Arredondar para cima

A função XLOOKUP é a sucessora ansiosamente aguardada para as funções VLOOKUP e HLOOKUP.

Vários exemplos foram usados ​​neste artigo para demonstrar as vantagens do XLOOKUP. Uma delas é que o XLOOKUP pode ser usado em planilhas, pastas de trabalho e também com tabelas. Os exemplos foram mantidos simples no artigo para ajudar a nossa compreensão.

Devido às matrizes dinâmicas serem introduzidas no Excel em breve, ele também pode retornar um intervalo de valores. Isso é definitivamente algo que vale a pena explorar mais.

Os dias de PROCV estão contados. O XLOOKUP está aqui e em breve será a fórmula de pesquisa de fato.