Como usar a função XLOOKUP no Microsoft Excel

O novo XLOOKUP do Excel substituirá VLOOKUP, fornecendo um substituto poderoso para uma das funções mais populares do Excel. Este novo recurso resolve algumas das limitações de VLOOKUP e tem funcionalidades adicionais. Isso é o que você precisa saber.

O que é XLOOKUP?

O novo recurso XLOOKUP tem correções para algumas das maiores limitações de VLOOKUP. Ao mesmo tempo, também substitui HLOOKUP. Como um exemplo, XLOOKUP pode olhar para a sua esquerda, por padrão é uma correspondência exata e permite que você especifique um intervalo de células em vez de um número de coluna. VLOOKUP não é tão fácil de usar ou tão versátil. Vamos mostrar como tudo funciona.

Por enquanto, XLOOKUP está habilitado apenas para usuários do programa Insiders. Qualquer um pode junte-se ao programa Insiders para acessar as funções mais recentes do Excel assim que estiverem disponíveis. Em breve, a Microsoft começará a implementá-lo para todos os usuários do Office 365.

Como usar a função XLOOKUP

Vamos mergulhar direto em 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 precisa de apenas três informações.

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

• Valor de pesquisa: O que você está buscando.
• Lookup_array: Onde olhar.
• 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 das vantagens que XLOOKUP tem sobre VLOOKUP aqui.

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

O infame terceiro argumento para VLOOKUP era especificar o número da coluna de informações a serem retornadas de uma matriz de tabela. Isso não é mais um obstáculo, pois o XLOOKUP permite que você escolha o intervalo do qual retornar. (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. Mais sobre isso abaixo.

Ao mesmo tempo, não tenho mais o problema de uma fórmula quebrada quando novas colunas são inseridas. Se isso aconteceu na 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ê tinha que especificar uma correspondência exata.

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

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

XLOOKUP pode olhar para a esquerda

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

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

No exemplo a seguir, precisamos procurar por um id (coluna E) e devolver o nome da pessoa (coluna D).

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

O que fazer se você não conseguir encontrar

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

Por isso, os usuários pesquisam rapidamente como ocultar esse erro porque ele não é correto nem útil. E, de qualquer forma, existem alternativas para fazer isso.

XLOOKUP vem com seu próprio argumento “se não for encontrado” integrado para lidar com esses erros. Vamos ver isso em ação com o exemplo acima, mas com um ID com erro ortográfico.

A próxima fórmula irá mostrar o texto “ID incorreta” 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

Mesmo que não seja tão comum quanto a correspondência exata, um uso muito eficaz de uma fórmula de pesquisa é pesquisar um valor em intervalos. Veja o seguinte exemplo. Queremos devolver o desconto com base no valor gasto.

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

XLOOKUP tem um quinto argumento opcional (lembrar, o padrão é a correspondência exata) chamado modo de correspondência.

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

Existe uma opção para encontrar a correspondência mais próxima menor que (-1) ou mais próximo maior que (1) o valor procurado. Também existe uma opção para usar caracteres curinga (2) O que? o la *. Esta configuração não é habilitada por padrão como acontecia com VLOOKUP.

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

Apesar disto, há um erro na célula C7 onde o erro é retornado # N / UMA (o argumento 'se não encontrado' não foi usado). Isso deveria ter retornado um 0% desligado porque você gasta 64 não atende aos critérios para nenhum desconto.

Outra vantagem da função SEARCH X é que você não precisa que o intervalo de pesquisa esteja em ordem crescente como VLOOKUP..

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

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

Pessoalmente, ainda classificaria a tabela pela coluna de pesquisa. Ter um “0” no fundo eu ficaria louco. Mas o fato de a fórmula não ter sido quebrada é 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 é uma pesquisa horizontal, usado para pesquisar em linhas.

Ele não é tão conhecido quanto seu irmão VLOOKUP, mas é útil para exemplos como o mostrado abaixo, onde os títulos estão na coluna A e os dados estão nas linhas 4 e 5.

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

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

XLOOKUP pode olhar de baixo para cima

Em geral, você deve pesquisar uma lista para encontrar o primeiro (frequentemente único) aparência de um valor. XLOOKUP tem um sexto argumento chamado modo de pesquisa. Isso nos permite mudar a pesquisa para começar na parte inferior e pesquisar uma lista para encontrar a última ocorrência de um valor.

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

A tabela de pesquisa é classificada por 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 SEARCH X oferece quatro opções. Estamos interessados ​​em usar a opção “Pesquise do último ao 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 argumentos foram ignorados. É opcional e queríamos o valor padrão de uma correspondência exata.

Arredondamento

A função XLOOKUP é o sucessor ansiosamente esperado às funções VLOOKUP e HLOOKUP.

Nesta postagem, vários exemplos foram usados ​​para demonstrar os benefícios do XLOOKUP. Um deles é que o XLOOKUP pode ser usado em planilhas, pastas de trabalho e também com tabelas. Os exemplos foram mantidos simples na postagem para ajudar a nossa compreensão.

Devido a matrizes dinâmicas que são inseridas no Excel cedo, ele também pode retornar uma gama de valores. Com certeza, isso é algo que é importante destacar para explorar mais.

VLOOKUP dias são numerados. XLOOKUP está aqui e em breve será a fórmula de pesquisa de fato.