Função XLOOKUP do Excel: Tudo o que você precisa saber (10 exemplos)

Assistir ao vídeo - Função XLOOKUP do Excel (10 exemplos XLOOKUP)

Excel Função XLOOKUP finalmente chegou.

Se você usa VLOOKUP ou INDEX / MATCH, tenho certeza de que vai adorar a flexibilidade que a função XLOOKUP oferece.

Neste tutorial, vou cobrir tudo o que há para saber sobre a função XLOOKUP e alguns exemplos que o ajudarão a saber como usá-la da melhor forma.

Então vamos começar!

O que é XLOOKUP?

XLOOKUP é uma nova função do Office 365 e é uma versão nova e aprimorada da função VLOOKUP / HLOOKUP.

Ele faz tudo o que VLOOKUP costumava fazer e muito mais.

XLOOKUP é uma função que permite a você procurar rapidamente um valor em um conjunto de dados (vertical ou horizontal) e retornar o valor correspondente em alguma outra linha / coluna.

Por exemplo, se você obteve as pontuações dos alunos em um exame, pode usar o XLOOKUP para verificar rapidamente a pontuação de um aluno usando o nome do aluno.

O poder desta função ficará ainda mais claro à medida que eu mergulhar fundo em alguns Exemplos de XLOOKUP posteriormente neste tutorial.

Mas antes de entrar nos exemplos, há uma grande questão - como obtenho acesso ao XLOOKUP?

Como obter acesso ao XLOOKUP?

A partir de agora, XLOOKUP está disponível apenas para usuários do Office 365.

Portanto, se você estiver usando versões anteriores do Excel (2010/2013/2016/2019), não poderá usar esta função.

Também não tenho certeza se isso seria lançado para versões anteriores ou não (talvez a Microsoft possa criar um suplemento da maneira que fez para o Power Query). Mas a partir de agora, você só pode usá-lo se estiver no Office 365.

Clique aqui para atualizar para o Office 365

Caso você já esteja no Office 365 (edição Home, Personal ou University) e não tenha acesso a ele, acesse a guia Arquivo e clique em Conta.

Haveria um programa Office Insider e você pode clicar e ingressar no Programa Office Insider. Isso lhe dará acesso à função XLOOKUP.

Espero que o XLOOKUP esteja disponível em todas as versões do Office 365 em breve.

Observação: XLOOKUP também está disponível para Office 365 para Mac e Excel para a Web (Excel online)

Sintaxe da função XLOOKUP

Abaixo está a sintaxe da função XLOOKUP:

= XLOOKUP (lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Se você usou PROCV, notará que a sintaxe é bastante semelhante, com alguns recursos adicionais incríveis, é claro.

Não se preocupe se a sintaxe e o argumento parecerem um pouco demais. Eu cubro isso com alguns exemplos fáceis de XLOOKUP posteriormente neste tutorial que o tornarão muito claro.

A função XLOOKUP pode contar 6 argumentos (3 obrigatórios e 3 opcionais):

1. lookup_value - o valor que você está procurando
2. lookup_array - a matriz na qual você está procurando o valor de pesquisa
3. return_array - a matriz da qual você deseja buscar e retornar o valor (correspondente à posição onde o valor de pesquisa é encontrado)
4. [if_not_found] - o valor a ser retornado caso o valor de pesquisa não seja encontrado. Caso você não especifique este argumento, um erro # N / A seria retornado
5. [match_mode] - Aqui você pode especificar o tipo de correspondência que deseja:
   • 0 - Correspondência exata, em que lookup_value deve corresponder exatamente ao valor em lookup_array. Esta é a opção padrão.
   • -1 - Procura a correspondência exata, mas se for encontrada, retorna o próximo item / valor menor
   • 1 - Procura a correspondência exata, mas se for encontrada, retorna o próximo item / valor maior
   • 2 - Para fazer a correspondência parcial usando curingas (* ou ~)
6. [search_mode] - Aqui você especifica como a função XLOOKUP deve pesquisar o lookup_array
   • 1 - Esta é a opção padrão onde a função começa a procurar o lookup_value do topo (primeiro item) para o fundo (último item) no lookup_array
   • -1 - Faz a pesquisa de baixo para cima. Útil quando você deseja encontrar o último valor correspondente no lookup_array
   • 2 - Executa uma pesquisa binária em que os dados precisam ser classificados em ordem crescente. Se não for classificado, pode gerar erros ou resultados incorretos
   • -2 - Executa uma pesquisa binária em que os dados precisam ser classificados em ordem decrescente. Se não for classificado, pode gerar erros ou resultados incorretos

Exemplos de função XLOOKUP

Agora vamos para a parte interessante - alguns exemplos práticos do XLOOKUP.

Esses exemplos irão ajudá-lo a entender melhor como XLOOKUP funciona, como ele é diferente de VLOOKUP e INDEX / MATCH e alguns aprimoramentos e limitações desta função.

Clique aqui para baixar o arquivo de exemplo e acompanhar

Exemplo 1: buscar um valor de pesquisa

Suponha que você tenha o seguinte conjunto de dados e deseja buscar a pontuação matemática para Greg (o valor de pesquisa).

Abaixo está a fórmula que faz isso:

= XLOOKUP (F2, A2: A15, B2: B15)

Na fórmula acima, acabei de usar os argumentos obrigatórios onde procura o nome (de cima para baixo), encontra uma correspondência exata e retorna o valor correspondente de B2: B15.

Uma diferença óbvia das funções XLOOKUP e VLOOKUP é a maneira como tratam a matriz de pesquisa. Em VLOOKUP, você tem toda a matriz em que o valor de pesquisa está na coluna mais à esquerda e, em seguida, especifica o número da coluna de onde deseja buscar o resultado. XLOOKUP, por outro lado, permite que você escolha lookup_array e return_array separadamente

Um benefício instantâneo de ter lookup_array e return_array como argumentos separados significa que agora você pode olhe para a esquerda. PROCV tinha esta limitação onde você só pode procurar e encontrar um valor que está à direita. Mas com o XLOOKUP, essa limitação desapareceu.

Aqui está um exemplo. Eu tenho o mesmo conjunto de dados, onde o nome está à direita e o return_range está à esquerda.

Abaixo está a fórmula que posso usar para obter a pontuação de Greg em matemática (o que significa olhar para a esquerda de lookup_value)

= XLOOKUP (F2, D2: D15, A2: A15)

O XLOOKUP resolve outro problema importante - no caso de você inserir uma nova coluna ou mover colunas, os dados resultantes ainda estarão corretos. PROCV provavelmente quebraria ou forneceria um resultado incorreto nesses casos, já que na maioria das vezes o valor do índice da coluna é embutido em código.

Exemplo 2: Pesquisar e buscar um registro inteiro

Vamos pegar os mesmos dados como exemplo.

Nesse caso, não quero apenas buscar a pontuação de Greg em matemática. Quero obter as pontuações em todas as disciplinas.

Nesse caso, posso usar a fórmula abaixo:

= XLOOKUP (F2, A2: A15, B2: D15)

A fórmula acima usa um intervalo return_array que é mais do que uma coluna (B2: D15). Portanto, quando o valor de pesquisa é encontrado em A2: A15, a fórmula retorna a linha inteira do return_array.

Além disso, você não pode excluir apenas as células que fazem parte da matriz que foi preenchida automaticamente. Neste exemplo, você não pode excluir H2 ou I2. Se você tentar, nada acontecerá. Se você selecionar essas células, a fórmula na barra de fórmulas ficará esmaecida (indicando que não pode ser alterada)

Você pode excluir a fórmula na célula G2 (onde a inserimos originalmente), isso excluirá o resultado inteiro.

Este é um aprimoramento útil, visto que anteriormente com VLOOKUP, você terá que especificar o número da coluna separadamente para cada fórmula.

Exemplo 3: pesquisa bidirecional usando XLOOKUP (pesquisa horizontal e vertical)

Abaixo está um conjunto de dados onde desejo saber a pontuação de Greg em matemática (o sujeito na célula G2).

Isso pode ser feito usando uma pesquisa bidirecional, em que procuro o nome na coluna A e o nome do assunto na linha 1. A vantagem dessa pesquisa bidirecional é que o resultado é independente do nome do aluno ou do assunto. Se eu alterar o nome do assunto para Química, essa fórmula XLOOKUP bidirecional ainda funcionará e fornecerá o resultado correto.

Abaixo está a fórmula que executará a pesquisa bidirecional e fornecerá o resultado correto:

= XLOOKUP (G1, B1: D1, XLOOKUP (F2, A2: A15, B2: D15))

Esta fórmula usa um XLOOKUP aninhado, onde primeiro eu o uso para buscar todas as marcas do aluno na célula F2.

Portanto, o resultado de XLOOKUP (F2, A2: A15, B2: D15) é {21,94,81}, que é uma matriz de marcas pontuadas por Greg neste caso.

Isso é então usado novamente na fórmula XLOOKUP externa como a matriz de retorno. Na fórmula XLOOKUP externa, procuro o nome do assunto (que está na célula G1) e a matriz de pesquisa é B1: D1.

Se o nome do assunto for Math, esta fórmula XLOOKUP externa busca o primeiro valor da matriz de retorno - que é {21,94,81} neste exemplo.

Isso faz o mesmo que foi, até agora, conseguido usando a combinação INDEX e MATCH

Clique aqui para baixar o arquivo de exemplo e acompanhar

Exemplo 4: Quando o valor de pesquisa não é encontrado (tratamento de erros)

O tratamento de erros foi adicionado à fórmula XLOOKUP.

O quarto argumento na função XLOOKUP é [if_not_found], onde você pode especificar o que deseja caso a pesquisa não seja encontrada.

Suponha que você tenha o conjunto de dados conforme mostrado abaixo, onde deseja obter a pontuação matemática no caso de correspondência, e caso o nome não seja encontrado, você deseja retornar - ‘Não apareceu’

A fórmula abaixo fará isso:

= XLOOKUP (F2, A2: A15, B2: B15, "Não apareceu")

Neste caso, codifiquei o que desejo obter, caso não haja correspondência. Você também pode usar uma referência de célula para uma célula ou fórmula.

Exemplo 5: XLOOKUP aninhado (pesquisa em intervalos múltiplos)

A genialidade de ter o argumento [if_not_found] é que ele permite que você use fórmula XLOOKUP aninhada.

Por exemplo, suponha que você tenha duas listas separadas, conforme mostrado abaixo. Embora eu tenha essas duas tabelas na mesma planilha, você pode tê-las em planilhas separadas ou até mesmo em pastas de trabalho.

Abaixo está a fórmula XLOOKUP aninhada que verificará o nome em ambas as tabelas e retornará o valor correspondente da coluna especificada.

= XLOOKUP (A12, A2: A8, B2: B8, XLOOKUP (A12, F2: F8, G2: G8))

Na fórmula acima, usei o argumento [if_not_found] para usar outra fórmula XLOOKUP. Isso permite adicionar o segundo XLOOKUP à mesma fórmula e digitalizar duas tabelas com uma única fórmula.

Não tenho certeza de quantos XLOOKUPs aninhados você pode usar em uma fórmula. Tentei até as 10 e deu certo, depois desisti 🙂

Exemplo 6: Encontre o último valor correspondente

Este era extremamente necessário e o XLOOKUP tornou isso possível. Agora você não precisa encontrar maneiras complicadas de obter o último valor correspondente em um intervalo.

Suponha que você tenha o conjunto de dados conforme mostrado abaixo e deseja verificar quando foi a última pessoa contratada em cada departamento e qual foi a data de contratação.

A fórmula abaixo pesquisará o último valor de cada departamento e fornecerá o nome da última contratação:

= XLOOKUP (F1, $ B $ 2: $ B $ 15, $ A $ 2: $ A $ 15 ,,, - 1)

E a fórmula abaixo fornecerá a data de contratação da última contratação para cada departamento:

= XLOOKUP (F1, $ B $ 2: $ B $ 15, $ C $ 2: $ C $ 15 ,,, - 1)

Como o XLOOKUP tem um recurso embutido para especificar a direção da pesquisa (do primeiro ao último ou do último ao primeiro), isso é feito com uma fórmula simples. Com dados verticais, VLOOKUP e INDEX / MATCH sempre olham de cima para baixo, mas com XLOOKUP e podem especificar a direção de baixo para cima também.

Exemplo 7: correspondência aproximada com XLOOKUP (encontrar taxa de imposto)

Outra melhoria notável com XLOOKUP é que agora existem quatro modos de correspondência (VLOOKUP tem 2 e MATCH tem 3).

Você pode especificar qualquer um dos quatro argumentos para decidir como o valor de pesquisa deve ser correspondido:

• 0 - Correspondência exata, em que lookup_value deve corresponder exatamente ao valor em lookup_array. Esta é a opção padrão.
• -1 - Procura a correspondência exata, mas se for encontrada, retorna o próximo item / valor menor
• 1 - Procura a correspondência exata, mas se for encontrada, retorna o próximo item / valor maior
• 2 - Para fazer a correspondência parcial usando curingas (* ou ~)

Mas a melhor parte é que você não precisa se preocupar se seus dados estão classificados em ordem crescente ou decrescente. Mesmo se os dados não forem classificados, o XLOOKUP cuidará disso.

Abaixo, tenho um conjunto de dados onde desejo encontrar a comissão de cada pessoa - e a comissão precisa ser calculada usando a tabela à direita.

Abaixo está a fórmula que fará isso:

= XLOOKUP (B2, $ E $ 2: $ E $ 6, $ F $ 2: $ F $ 6,0, -1) * B2

Isso simplesmente usa o valor de vendas como a pesquisa e examina a tabela de pesquisa à direita. Nesta fórmula, usei -1 como o quinto argumento ([match_mode]), o que significa que ele irá procurar uma correspondência exata e, quando não encontrar uma, retornará o valor apenas menor que o valor de pesquisa .

E como eu disse, você não precisa se preocupar se seus dados estão classificados ou não.

Clique aqui para baixar o arquivo de exemplo e acompanhar

Exemplo 8: pesquisa horizontal

XLOOKUP pode fazer pesquisas tanto verticais quanto horizontais.

Abaixo, tenho um conjunto de dados onde tenho os nomes dos alunos e suas pontuações em linhas e desejo buscar a pontuação para o nome na célula B7.

A fórmula abaixo fará isso:

= XLOOKUP (B7, B1: O1, B2: O2)

Isso nada mais é do que uma pesquisa simples (semelhante ao que vimos no Exemplo 1), mas horizontal.

Todos os exemplos que abordo sobre pesquisa vertical também podem ser feitos com uma pesquisa horizontal usando XLOOKUP (adeus a VLOOKUP e HLOOKUP).

Exemplo 9: Pesquisa condicional (usando XLOOKUP com outras fórmulas)

Este é um exemplo um pouco avançado e também mostra o poder do XLOOKUP quando você precisa fazer pesquisas complexas.

Abaixo está um conjunto de dados onde tenho os nomes dos alunos e suas pontuações, e desejo saber o nome do aluno que obteve a pontuação máxima em cada matéria e a contagem de alunos que pontuaram mais de 80 em cada matéria.

Segue abaixo a fórmula que dará o nome do aluno com as notas mais altas em cada disciplina:

= XLOOKUP (MAX (XLOOKUP (G1, $ B $ 1: $ D $ 1, $ B $ 2: $ D $ 15)), XLOOKUP (G1, $ B $ 1: $ D $ 1, $ B $ 2: $ D $ 15), $ A $ 2: $ A $ 15)

Como XLOOKUP pode ser usado para retornar uma matriz inteira, usei-o para primeiro obter todas as notas para o assunto necessário.

Por exemplo, para matemática, quando eu uso XLOOKUP (G1, $ B $ 1: $ D $ 1, $ B $ 2: $ D $ 15), ele me dá todas as pontuações em matemática. Posso então usar a função MAX para encontrar a pontuação máxima neste intervalo.

Essa pontuação máxima se torna meu valor de pesquisa e o intervalo de pesquisa seria a matriz retornada por XLOOKUP (G1, $ B $ 1: $ D $ 1, $ B $ 2: $ D $ 15)

Eu uso isso em outra fórmula XLOOKUP para buscar o nome do aluno que obteve as notas máximas.

E para contar o número de alunos que pontuaram mais de 80, use a fórmula abaixo:

= CONT.SE (XLOOKUP (G1, $ B $ 1: $ D $ 1, $ B $ 2: $ D $ 15), "> 80")

Este simplesmente usa a fórmula XLOOKUP para obter um intervalo de todos os valores para o assunto em questão. Em seguida, envolve-o na função CONT.SE para obter o número de pontuações superiores a 80.

Exemplo 10: Usando curinga em XLOOKUP

Assim como você pode usar caracteres curinga em VLOOKUP e MATCH, você também pode fazer isso com XLOOKUP.

Mas há uma diferença.

Em XLOOKUP, você precisa especificar que está usando caracteres curinga (no quinto argumento). Se você não especificar isso, XLOOKUP apresentará um erro.

Abaixo está um conjunto de dados onde tenho os nomes das empresas e sua capitalização de mercado.

Desejo pesquisar o nome de uma empresa na coluna D e obter a capitalização de mercado na tabela à esquerda. E como os nomes na coluna D não são correspondências exatas, terei que usar caracteres curinga.

Abaixo está a fórmula que fará isso:

= XLOOKUP ("*" & D2 & "*", $ A $ 2: $ A $ 11, $ B $ 2: $ B $ 11,, 2)

Na fórmula acima, usei o caractere curinga asterisco (*) antes e depois de D2 (ele precisa estar entre aspas duplas e unido a D2 usando "e" comercial).

Isso diz à fórmula para examinar todas as células e, se ela contiver a palavra na célula D2 (que é Apple), considere-a uma correspondência exata. Não importa quantos e quais caracteres existem antes e depois do texto na célula D2.

E para ter certeza de que o XLOOKUP aceita caracteres curinga, o quinto argumento foi definido como 2 (correspondência de caractere curinga).

Exemplo 11: Encontre o Último Valor na Coluna

Como o XLOOKUP permite pesquisar de baixo para cima, você pode encontrar facilmente o último valor em uma lista, bem como buscar o valor correspondente em uma coluna.

Suponha que você tenha um conjunto de dados conforme mostrado abaixo e deseja saber qual é a última empresa e qual é a capitalização de mercado desta última empresa.

A fórmula abaixo fornecerá o nome da última empresa:

= XLOOKUP ("*", A2: A11, A2: A11,, 2, -1)

E a fórmula abaixo fornecerá o valor de mercado da última empresa da lista:

= XLOOKUP ("*", A2: A11, B2: B11,, 2, -1)

Essas fórmulas novamente usam caracteres curinga. Nestes, usei asterisco (*) como o valor de pesquisa, o que significa que isso consideraria a primeira célula encontrada como uma correspondência exata (como asterisco pode ser qualquer caractere e qualquer número de caracteres).

E como a direção é de baixo para cima (para os dados organizados verticalmente), ele retornará o último valor da lista.

E a segunda fórmula, desde então, usa um intervalo_de_retorno separado para obter a capitalização de mercado do sobrenome na lista.

Clique aqui para baixar o arquivo de exemplo e acompanhar

E se você não tiver o XLOOKUP?

Como o XLOOKUP provavelmente estará disponível apenas para usuários do Office 365, uma maneira de obtê-lo é atualizando para o Office 365.

Se você já tem a edição Office 365 Home, Personal ou University, já tem acesso ao XLOOKUP. Tudo o que você precisa fazer é ingressar no programa Office Insider.

Para fazer isso, vá para a guia Arquivo, clique em Conta e, a seguir, clique na opção Office insider. Haveria a opção de ingressar no programa de insider.

Caso você tenha outras assinaturas do Office 365 (como Enterprise), tenho certeza que XLOOKUP e outros recursos incríveis (como matrizes dinâmicas, fórmulas como SORT e FILTER) estarão disponíveis em breve.

Caso você esteja usando o Excel 2010/2013/2016/2019, não terá XLOOKUP e terá que continuar a usar a combinação VLOOKUP, HLOOKUP e INDEX / MATCH para obter o melhor das fórmulas de pesquisa.

Compatibilidade com versões anteriores de XLOOKUP

Isso é algo que você precisa ter cuidado - XLOOKUP é NÃO compatível com versões anteriores.

Isso significa que se você criar um arquivo e usar a fórmula XLOOKUP e, em seguida, abri-lo em uma versão que não tem XLOOKUP, ele mostrará erros.

Como o XLOOKUP é um grande passo na direção certa, acredito que essa se tornará a fórmula de pesquisa padrão, mas certamente levará alguns anos antes de ser amplamente adotada. Afinal, ainda vejo algumas pessoas usando o Excel 2003.

Portanto, estes são 11 exemplos do XLOOKUP que podem ajudá-lo a fazer todas as pesquisas e referências feitas com mais rapidez e também torná-lo fácil de usar.

Espero que você tenha achado este tutorial útil!