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.
últimas postagens
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. Vamos mostrar como tudo funciona.
No momento, o XLOOKUP está disponível apenas para usuários do programa Insiders. Qualquer um pode junte-se ao 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. 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 nos concentrar neles:
Valor_procurado: 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 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.
XLOOKUP vem com seu próprio argumento embutido “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 uma opção para 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 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, a seguir, 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 a fórmula não ter quebrado é 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 concluída é 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 esperado 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 em tabelas. Os exemplos foram mantidos simples no artigo para ajudar nosso entendimento.
Devido a matrizes dinâmicas sendo 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 VLOOKUP estão contados. XLOOKUP está aqui e em breve será a fórmula de pesquisa de fato.