Excel 2020: Doze Benefícios do XLOOKUP - Dicas do Excel

A nova função XLOOKUP está sendo implementada no Office 365 a partir de novembro de 2019. Joe McDaid, da equipe do Excel, projetou o XLOOKUP para unificar as pessoas que usam VLOOKUP e as pessoas que usam INDEX / MATCH. Esta seção discutirá os 12 benefícios do XLOOKUP:

  1. A correspondência exata é o padrão.
  2. O terceiro argumento baseado em inteiro de VLOOKUP agora é uma referência adequada.
  3. IFNA é integrado para lidar com valores ausentes.
  4. XLOOKUP não tem problema em ir para a esquerda.
  5. Encontre a próxima correspondência menor ou a próxima maior sem classificar a tabela.
  6. XLOOKUP pode fazer HLOOKUP.
  7. Encontre a última correspondência pesquisando na parte inferior.
  8. Os curingas estão "desativados" por padrão, mas você pode ativá-los novamente.
  9. Retorne todos os 12 meses em uma única fórmula.
  10. Pode retornar uma referência de célula se XLOOKUP estiver próximo a dois pontos, como XLOOKUP (); XLOOKUP ()
  11. Pode fazer uma correspondência bidirecional como INDEX (, MATCH, MATCH) pode fazer.
  12. Pode somar todas as pesquisas em uma única fórmula, como o LOOKUP poderia fazer.

Aqui está a sintaxe: = XLOOKUP (Lookup_Value, Lookup_Array, Results_Array, (if_not_found), (match_mode), (search_mode)).

Benefício 1 do XLOOKUP: correspondência exata por padrão

99% das minhas fórmulas VLOOKUP terminam em FALSE ou 0 para indicar uma correspondência exata. Se você sempre usa a versão de correspondência exata de VLOOKUP, pode começar a deixar o match_mode fora de sua função XLOOKUP.

Na figura a seguir, você está observando W25-6 da célula A4. Você deseja procurar esse item em L8: L35. Quando for encontrado, você deseja o preço correspondente da coluna N. Não há necessidade de especificar False como match_mode porque o padrão de XLOOKUP é uma correspondência exata.

XLOOKUP o valor em A4. Veja em L8: L35. Retorne o preço correspondente de N8: N35.

Benefício 2 de XLOOKUP: o Results_Array é uma referência em vez de um inteiro

Pense na fórmula VLOOKUP que você usaria antes de XLOOKUP. O terceiro argumento seria 3 para indicar que você queria retornar a 3ª coluna. Sempre houve o perigo de que um colega de trabalho sem noção tivesse inserido (ou excluído) uma coluna em sua tabela. Com uma coluna extra na tabela, o VLOOKUP que estava retornando um preço começaria a retornar uma descrição. Como XLOOKUP estava apontando para uma referência de célula, a fórmula se reescreve para continuar apontando para o preço que agora está na coluna O.

O VLOOKUP antigo falharia se alguém inserisse uma nova coluna na tabela de pesquisa. XLOOKUP continua funcionando.

Benefício 3 do XLOOKUP: IFNA é integrado como um argumento opcional

O temido erro # N / A é retornado quando o valor de pesquisa não é encontrado na tabela. No passado, para substituir # N / A por outra coisa, você teria que usar IFERROR ou IFNA agrupado em VLOOKUP.

Quando um item não é encontrado, ele retorna # N / A de VLOOKUP ou XLOOKUP …

Graças a uma sugestão de Rico em meu canal no YouTube, a equipe do Excel incorporou um quarto argumento opcional para if_not_found. Se você quiser substituir esses erros # N / A por zero, basta adicionar 0 como o quarto argumento. Ou você pode usar algum texto, como "Valor não encontrado".

O quarto argumento opcional em XLOOKUP é "se não for encontrado". Coloque um 0 ou "Não encontrado" ali.

Benefício 4 do XLOOKUP: sem problemas olhar para a esquerda do campo-chave

PROCV não pode olhar para a esquerda do campo-chave sem recorrer a PROCV (A4, ESCOLHER ((1,2), G7: G34, F7: F34), 2, Falso). Com o XLOOKUP, não há problema em ter o Results_array à esquerda do Lookup_array.

Com o XLOOKUP, não há problema em retornar a categoria da coluna F ao procurar os números das peças na coluna G. Esse sempre foi o ponto fraco de VLOOKUP: ele não conseguia olhar para a esquerda.

Benefício 5 do XLOOKUP: correspondência do próximo menor ou do próximo maior sem classificação

VLOOKUP tinha a opção de procurar a correspondência exata ou apenas um valor menor. Você pode deixar o quarto argumento fora de VLOOKUP ou alterar False para True. Para que isso funcione, a tabela de pesquisa deve ser classificada em sequência crescente.

Um exemplo da versão de correspondência aproximada de VLOOKUP. Qualquer venda de 10 mil a 20 mil recebe um bônus de $ 12.

Mas VLOOKUP não tinha a capacidade de retornar a correspondência exata ou o próximo item maior. Para isso, você tinha que passar a usar MATCH com -1 como match_mode e ter cuidado para que a tabela de pesquisa fosse classificada de forma descendente.

O quinto argumento opcional match_mode do XLOOKUP pode procurar apenas a correspondência exata, igual ou apenas menor, igual ou apenas maior. Observe que os valores em XLOOKUP fazem mais sentido do que em MATCH:

  • -1 encontra o valor igual ou apenas menor
  • 0 encontra uma correspondência exata
  • 1 encontra o valor igual ou apenas maior.

Mas, a parte mais incrível: a tabela de pesquisa não precisa ser classificada e qualquer match_mode funcionará.

Abaixo, um match_mode de -1 encontra o próximo item menor.

O quinto argumento de XLOOKUP é Match_Mode. 0 é para correspondência exata. O negativo é usado para correspondência exata ou próximo item menor. Positivo 1 é para correspondência exata ou próximo item maior. 2 é para Correspondência de curinga. Para espelhar o que VLOOKUP com True no quarto argumento faria, coloque um negativo como o argumento match_mode em XLOOKUP.

Aqui, um match_mode de 1 encontra o veículo necessário, dependendo do número de pessoas no grupo. Observe que a tabela de pesquisa não é classificada por passageiros e o nome do veículo está à esquerda da chave.

XLOOKUP pode fazer algo que VLOOKUP não pôde fazer: encontrar a correspondência exata ou apenas maior. Nesse caso, uma empresa de turismo tem uma lista de reservas. Com base no número de passageiros, a tabela de pesquisa mostra qual veículo você precisa para essas pessoas.

A tabela diz:

  • Ônibus para 64 pessoas
  • Carro para 4 pessoas
  • Motorcyle detém 1 pessoa
  • Van de turismo para 12 pessoas
  • Van com capacidade para 6 pessoas.

Como bônus, os dados são classificados por Veículo (na solução antiga, usando MATCH, a tabela teria que ser classificada em ordem decrescente por Capacidade. Além disso: o Veículo está à esquerda de Capacidade.

Benefício 6 do XLOOKUP: O XLOOKUP lateralmente substitui o HLOOKUP

O lookup_array e results_array podem ser horizontais com XLOOKUP, tornando simples substituir HLOOKUP.

Aqui, a tabela de pesquisa é horizontal. No passado, isso exigiria HLOOKUP, mas XLOOKUP pode lidar com uma tabela que vai para o lado.

Benefício 7 do XLOOKUP: pesquise na parte inferior para obter a correspondência mais recente

Tenho um vídeo antigo no YouTube respondendo a uma pergunta de uma fazenda de cavalos britânica. Eles tinham uma frota de veículos. Cada vez que um veículo entrava em busca de combustível ou serviço, eles registravam o veículo, a data e a quilometragem em uma planilha. Eles queriam encontrar a última quilometragem conhecida para cada veículo. Embora o MAXIFS da era Excel-2017 pudesse resolver isso hoje, a solução muitos anos atrás era uma fórmula misteriosa usando LOOKUP e envolvia divisão por zero.

Hoje, o sexto argumento opcional de XLOOKUP permite especificar que a pesquisa deve começar na parte inferior do conjunto de dados.

Encontre a última correspondência na lista.

Nota

Embora seja uma grande melhoria, permite apenas encontrar a primeira ou a última correspondência. Algumas pessoas esperavam que isso deixasse você encontrar a segunda ou terceira correspondência, mas essa não é a intenção do argumento search_mode.

Cuidado

A figura acima mostra que existem modos de pesquisa usando a antiga pesquisa binária. Joe McDaid desaconselha seu uso. Primeiro, o algoritmo de pesquisa aprimorado de 2018 é rápido o suficiente para que não haja nenhum benefício significativo de velocidade. Em segundo lugar, você corre o risco de um colega de trabalho sem noção classificando a tabela de pesquisa e apresentando respostas erradas.

Benefício 8 do XLOOKUP: os caracteres curinga são "desativados" por padrão

A maioria das pessoas não percebeu que VLOOKUP está tratando asterisco, ponto de interrogação e til como caracteres curinga, conforme descrito em "Nº 51 Usar um curinga em VLOOKUP" na página 143. Com XLOOKUP, os curingas são desativados por padrão. Se você quiser que o XLOOKUP trate esses caracteres como curinga, use 2 como Match_Mode.

Muito poucas pessoas perceberam que VLOOKUP está tratando os asteriscos no valor de pesquisa como um caractere curinga. Por padrão, XLOOKUP não está usando curingas, mas você pode forçá-lo a se comportar como VLOOKUP se usar um modo de correspondência 2: Correspondência de caracteres curinga.

Benefício 9 do XLOOKUP: Retorne todos os 12 meses em uma única fórmula!

Este é realmente um benefício dos Dynamic Arrays, mas é meu motivo favorito para adorar o XLOOKUP. Quando você precisa retornar todos os 12 meses em uma pesquisa, uma única fórmula inserida em B6 com um return_array retangular retornará vários resultados. Esses resultados se espalharão para as células adjacentes.

Na figura abaixo, uma única fórmula inserida em B7 retorna todas as 12 respostas mostradas em B7: M7.

Um único XLOOKUP na coluna de janeiro retorna números de janeiro a dezembro. Isso é feito especificando um results_array com 12 colunas.

Benefício 10 do XLOOKUP: pode retornar uma referência de célula se adjacente ao cólon

Este é complexo, mas bonito. No passado, havia sete funções que mudariam de retornar um valor de célula para retornar uma referência de célula se a função tocasse dois pontos. Para obter um exemplo, consulte Use A2: INDEX () como um OFFSET não volátil. XLOOKUP é a função de oitos para oferecer esse comportamento, juntando CHOOSE, IF, IFS, INDEX, INDIRECT, OFFSET e SWITCH.

Considere a seguinte figura. Alguém seleciona Cherry em E4 e Fig em E5. Você quer uma fórmula que some tudo de B6 a B9.

A figura mostra duas fórmulas XLOOKUP em duas células. O primeiro retorna 15 da célula B6. O segundo retrocede 30 de B9. Mas então, em uma terceira célula, há uma fórmula que une as duas fórmulas XLOOKUP com dois pontos e depois envolve isso em uma função SUM. O resultado é a SOMA de B6: B9, porque XLOOKUP pode retornar uma referência de célula se a função aparecer ao lado de um operador, como dois pontos. Para provar que isso está funcionando, as próximas várias figuras mostrarão essa fórmula na caixa de diálogo Avaliar fórmula.

Na figura acima, você pode ver que um XLOOKUP de E4 retornará o 15 da célula B6. Um XLOOKUP de E5 retornará o 30 de B9. No entanto, se você pegar as duas funções XLOOKUP das células D9 e D10 e colocá-las juntas com dois pontos no meio, o comportamento de XLOOKUP será alterado. Em vez de retornar 15, o primeiro XLOOKUP retorna o endereço da célula B6!

Para provar isso, selecionei D7 e uso Fórmulas, Avaliar Fórmula. Após pressionar Avaliar duas vezes, a próxima parte a ser calculada é XLOOKUP ("Cereja", A4: A29, B4: B29), conforme mostrado aqui.

Isso mostra a caixa de diálogo Avaliar fórmula antes de avaliar o primeiro XLOOKUP. Este XLOOKUP aparece antes de dois pontos.

Pressione Avaliar novamente e, surpreendentemente, a fórmula XLOOKUP retorna $ B $ 6 em vez dos 15 armazenados em B6. Isso acontece porque há dois pontos imediatamente após a fórmula XLOOKUP.

Clique em Avaliar e o primeiro XLOOKUP retorna $ B $ 6 em vez de 15.

Pressione Avaliar mais duas vezes e a fórmula provisória será = SUM (B6: B9).

Depois de avaliar o segundo XLOOKUP, a fórmula provisória é = SUM (B6: B9).

Este é um comportamento incrível que a maioria das pessoas não conhece. Excel MVP Charles Williams me disse que ele pode ser acionado com qualquer um desses três operadores ao lado de XLOOKUP:

  • Cólon
  • Espaço (operador de interseção)
  • Vírgula (operador Union)

Benefício 11 do XLOOKUP: correspondência bidirecional como INDEX (, MATCH, MATCH)

Para todos os meus amigos VLOOKUP, as pessoas da INDEX / MATCH estão esperando para ver se XLOOKUP pode lidar com uma correspondência bidirecional. A grande notícia: pode fazer isso. A má notícia: a metodologia é um pouco diferente do que os fãs do INDEX / MATCH esperariam. Pode ser um pouco além de suas cabeças. Mas tenho certeza de que eles podem usar esse método.

Para uma correspondência bidirecional, você deseja descobrir qual linha contém o número da conta A621 mostrado em J3. Então, o XLOOKUP começa fácil: = XLOOKUP (J3, A5: A15. Mas então você tem que fornecer um results_array. Você pode usar o mesmo truque do XLOOKUP Benefício 9: Retornar todos os 12 meses em uma única fórmula acima, mas use-o para retornar um vetor vertical. Um XLOOKUP interno procura o mês J4 nos cabeçalhos do mês em B4: G4. O return_array é especificado como B5: G15. O resultado é que o XLOOKUP interno retorna uma matriz como aquela mostrada em I10 : I20 abaixo. Como A621 é encontrado na quinta célula do lookup_array e 104 é encontrado na quinta célula do results_array, você obtém a resposta correta da fórmula. Abaixo, J6 mostra a forma antiga. J7 retorna a nova forma.

XLookup J3 na lista de contas em A5: A15. Para a Matriz de resultados, use XLOOKUP (J4, B4: G4, B5: G15). Nesta fórmula, B4: G4 é uma lista de meses. B5: G15 é a matriz retangular de valores para todas as contas de todos os meses. Em outra célula, apenas o XLOOKUP interno mostra como ele retorna toda a coluna de valores de maio.

Benefício 12 do XLOOKUP: soma todos os valores de pesquisa em uma única fórmula

A antiga função LOOKUP oferecia dois truques estranhos. Primeiro, se você estiver tentando descobrir o valor total da despesa de bônus a acumular, pode pedir a LOOKUP para pesquisar todos os valores em uma única fórmula. Na imagem abaixo, LOOKUP (C4: C14 está fazendo 11 pesquisas. Mas a função LOOKUP não oferece uma correspondência exata e exige que a tabela de pesquisa seja classificada.

Procure 13 valores e some-os. Isso costumava funcionar com LOOKUP, mas também funciona com XLOOKUP. Especifique todos os valores de pesquisa C4: C14 como o primeiro argumento. Envolva o XLOOKUP em uma função SUM.

Com XLOOKUP, você pode especificar um intervalo como lookup_value e XLOOKUP retornará todas as respostas. A vantagem é que o XLOOKUP pode fazer correspondências exatas.

O truque de usar LOOKUP para somar todos os resultados de pesquisa funcionou apenas com a versão de correspondência aproximada de Lookup. Aqui, XLOOKUP está fazendo uma correspondência exata em todos os nomes em L4: L14 e obtendo um total de todos os resultados.

Dica de bônus: que tal um lookup torcido?

Excel MVP Mike Girvin freqüentemente mostra um truque da função LOOKUP, onde Lookup_Vector é vertical e Result_Vector é horizontal. XLOOKUP não oferecerá suporte nativo a esse truque. Mas, se você trapacear um pouco e envolver o results_array na função TRANSPOSE, poderá gerenciar uma pesquisa distorcida.

Aqui, a matriz de pesquisa é vertical e a matriz de resultados é horizontal. A antiga função LOOKUP pode lidar com isso, mas para fazer isso com XLOOKUP, você deve envolver qualquer array em TRANSPOSE.

Artigos interessantes...