agwebrunner.com

O novo XLOOKUP do Excel substituirá VLOOKUP, fornecendo um poderoso substituto para uma das funções mais populares do Excel. Esta nova função resolve algumas das limitações de VLOOKUP e tem 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 de VLOOKUP. Além disso, ele 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. 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 ingressar no programa Insiders para acessar os recursos 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 com um exemplo de XLOOKUP em ação. Veja 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 nos concentrar neles:

• Valor_procurado: O que você está procurando.
• 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 vantagens de XLOOKUP sobre VLOOKUP aqui.

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

O infame terceiro argumento 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 a partir do qual retornar (coluna F neste exemplo).

E não se esqueça, XLOOKUP pode visualizar os dados restantes da célula selecionada, ao contrário de 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 se ajustaria automaticamente.

Correspondência exata é o padrão

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

Felizmente, 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.

Resumindo, XLOOKUP faz menos perguntas do que VLOOKUP, é mais amigável e também mais durável.

XLOOKUP pode olhar para a esquerda

Ser capaz de selecionar 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 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 conseguir isso: = XLOOKUP (A2, $ E $ 2: $ E $ 8, $ D $ 2: $ D $ 8)

O que fazer se não for encontrado

Os usuários das funções de pesquisa estão muito familiarizados com a mensagem de erro # N / A que os saúda quando sua VLOOKUP ou função 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 ele não é correto ou útil. E, claro, existem maneiras de fazer isso.

O XLOOKUP vem com seu próprio argumento interno “se não encontrado” 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 incorreta” em vez da mensagem de erro:= XLOOKUP (A2, $ E $ 2: $ E $ 8, $ D $ 2: $ D $ 8, "ID incorreta")

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. Veja o seguinte exemplo. Queremos devolver o desconto dependente do valor gasto.

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

XLOOKUP tem um quinto argumento opcional (lembre-se, o padrão é a correspondência exata) denominado modo de correspondência.

Você pode ver que XLOOKUP tem mais recursos com correspondências aproximadas do que 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. Também existe a opção de usar caracteres curinga (2), como o? ou o *. Esta configuração não está ativada por padrão como era com VLOOKUP.

A fórmula neste exemplo retorna o menor mais próximo do que o 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 atende aos 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, 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 problema ter o “0” na parte inferior da faixa.

Pessoalmente, 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

Conforme 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 como seu irmão VLOOKUP, mas é útil para exemplos como abaixo, onde os cabeçalhos estão na coluna A e os dados estão ao longo das linhas 4 e 5.

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 venda relacionado ao nome na célula A2. Ele olha ao longo da linha 4 para encontrar 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 (geralmente única) ocorrência de um valor. XLOOKUP tem um sexto argumento denominado modo de pesquisa. Isso nos permite alternar a pesquisa para começar na parte inferior e pesquisar 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 último para primeiro”.

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

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

Arredondar para cima

A função XLOOKUP é o sucessor ansiosamente aguardado 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 nosso entendimento.

Como as matrizes dinâmicas serão introduzidas no Excel em breve, ele também pode retornar uma faixa de valores. Isso é definitivamente algo que vale a pena explorar mais.

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