Como usar a função XLOOKUP no Microsoft Excel
O novo XLOOKUP do Excel substituirá o VLOOKUP, fornecendo um poderoso substituto para uma das funções mais populares do Excel. Esta nova função resolve algumas das limitações do VLOOKUP e possui funcionalidade extra. Aqui está o que você precisa saber.
O que é o XLOOKUP?
A nova função XLOOKUP possui soluções para algumas das maiores limitações do VLOOKUP. Além disso, ele também substitui o HLOOKUP. Por exemplo, o XLOOKUP pode olhar para a esquerda, o padrão é uma correspondência exata e permite especificar 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 no programa Insiders. Qualquer pessoa pode participar do programa Insiders para acessar os recursos mais recentes do Excel assim que estiverem disponíveis. Em breve, a Microsoft começará a lançá-lo para todos os usuários do Office 365.
Como usar a função XLOOKUP
Vamos mergulhar de frente 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 o XLOOKUP com cinco argumentos, mas apenas os três primeiros são necessários para uma correspondência exata. Então, vamos nos focar neles:
- Valor_processo: O que você está procurando.
- Modelo_array: onde procurar.
- Modelo_trabalho:o intervalo que contém o valor a ser retornado.
A fórmula a seguir 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 índice de colunasNúmero
O terceiro argumento infame do VLOOKUP foi especificar o número da coluna das informações a serem retornadas de uma matriz de tabelas. Isso não é mais um problema, porque o XLOOKUP permite selecionar o intervalo para o qual retornar (coluna F neste exemplo).
E não se esqueça, o XLOOKUP podevisualize 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 na sua planilha, o intervalo de retorno seria ajustado automaticamente.
A correspondência exata é o padrão
Sempre foi confuso ao aprender o VLOOKUP por que vocêteve que especificar que uma correspondência exata era desejada.
Felizmente, o XLOOKUP usa como padrão uma correspondência exata — o motivo muito mais comum para usar uma fórmula de pesquisa). Isso reduz a necessidade de responder ao quarto argumento e garante menos erros para os novos usuários da fórmula.
Portanto, em resumo, o XLOOKUP faz menos perguntas que o VLOOKUP, é mais fácil de usar e também é mais durável.
O XLOOKUP pode olhar para a esquerda
A possibilidade de selecionar um intervalo de pesquisa torna o XLOOKUP mais versátil que o VLOOKUP. Com o XLOOKUP, a ordem das colunas da tabela não importa.
O VLOOKUP foi restringido pesquisando a coluna mais à esquerda de uma tabela e retornando de um número especificado de colunas para a direita.
No exemplo abaixo, precisamos procurar 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)
Usando o XLOOKUP para uma pesquisa de intervalo
não tão comum quanto a correspondência exata, o uso muito eficaz de uma fórmula de pesquisa é procurar um valor em intervalos. Veja o exemplo a seguir. 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 se enquadram nos intervalos da coluna E. Isso determinará o desconto obtido.
XLOOKUP tem um quarto argumento opcional (lembre-se, eleo padrão é a correspondência exata) denominada modo de correspondência.
Você pode ver que o XLOOKUP possui mais recursos com correspondências aproximadas do que o VLOOKUP.
a opção de encontrar a correspondência mais próxima menor que (-1) ou maior que (1) o valor procurado.Há também uma opção para usar caracteres curinga (2) como?ou o *. Essa configuração não está ativada por padrão, como estava com o VLOOKUP.
A fórmula neste exemplo retorna o menor 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 em que o erro # N / A é retornado. Isso deveria ter retornado um desconto de 0% porque o gasto 64 não atinge os critérios para qualquer desconto.
Outra vantagem da função XLOOKUP é que ela não exige que o intervalo de pesquisa esteja em ordem crescente, como o VLOOKUP faz..
Digite 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. Tendo “ 0 ”no fundo me deixaria louca. Mas o fato de a fórmula não ter quebrado é brilhante.
XLOOKUP substitui a função HLOOKUP também
Como mencionado, a função XLOOKUP também está aqui para substituir o 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ãocoluna A, e os dados estão ao longo das linhas 4 e 5.
XLOOKUP pode olhar nas duas direções & # 8211;colunas para baixo e também ao longo de 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 procurar emthe Bottom-Up
Normalmente, você precisa procurar uma lista para encontrar a primeira (muitas vezes apenas) ocorrência de um valor. XLOOKUP possui um quinto argumento chamado modo de pesquisa. Isso nos permite alternar a pesquisa para começar 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 de cada produtona coluna A.
A tabela de pesquisa está na ordem da data e há várias verificações de estoque por produto. Queremos retornar o nível de estoque da última vez em que foi verificado (última ocorrência do ID do produto).
O quinto argumento da função XLOOKUP fornece quatro opções. Estamos interessados em usar a “ Pesquisa por penúltimo a ”opção.
A fórmula completa é mostrada aqui: = XLOOKUP (A2, $ E $ 2: $ E $ 9, $ F $ 2: $ F $ 9 ,, - 1)
Nesta fórmula, o quarto argumento foi ignorado.É opcional e queríamos o padrão de uma correspondência exata.
Arredondamento
A função XLOOKUP é o sucessor ansiosamente aguardado das 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.
Devido à introdução dinâmica de matrizes dinâmicas no Excel, ele também pode retornar um intervalo de valores. Definitivamente, é algo que vale a pena explorar mais.
Os dias do VLOOKUP estão contados.O XLOOKUP está aqui e em breve será a fórmula de pesquisa de fato.
Via: How to Geek
Veja Também:
Nenhum comentário