Como usar a função XLOOKUP no Excel 2016

Excel 2016 para assinantes do Office 365 no Windows e Mac agora oferece suporte a uma nova função XLOOKUP, apresentada como um substituto consideravelmente mais simples e versátil para a função de pesquisa vertical muito popular (embora muitas vezes difamada), VLOOKUP (não sei o que é o X em XLOOKUP significa; extensivo, talvez?).

Para aqueles que ainda não estão familiarizados com VLOOKUP (considerada a terceira função mais usada logo após SOMA e MÉDIA), esta função pesquisa verticalmente por linha na coluna mais à esquerda de uma tabela de pesquisa designada de cima para baixo até encontrar um valor em um coluna de pesquisa designada por um número de deslocamento que corresponde ou excede aquele que você está procurando. Embora extremamente útil para localizar itens específicos em uma longa lista ou coluna de uma tabela de dados em sua planilha, a função VLOOKUP tem várias limitações não compartilhadas por esta nova função de pesquisa, como XLOOKUP:

• O padrão é encontrar correspondências exatas para o seu valor de pesquisa no intervalo de pesquisa
• Pode pesquisar tanto verticalmente (por linha) quanto horizontalmente (por coluna) em uma tabela, substituindo assim a necessidade de usar a função HLOOKUP ao pesquisar horizontalmente por coluna
• Pode pesquisar à esquerda ou à direita para que o intervalo de pesquisa em sua tabela de pesquisa não precise estar localizado em uma coluna à esquerda daquela designada como o intervalo de retorno para que a função funcione
• Quando o padrão de correspondência exata é usado, funciona mesmo quando os valores no intervalo de pesquisa não são classificados em uma ordem específica
• Pode pesquisar da linha inferior ao superior no intervalo da matriz de pesquisa, usando um argumento de modo de pesquisa opcional

A função XLOOKUP tem cinco argumentos possíveis, os três primeiros são obrigatórios e os dois últimos opcionais, usando a seguinte sintaxe:

XLOOKUP ( valor_procurado , matriz_consulta , return_array , [ match_mode ], [ search_mode ])

O argumento lookup_value obrigatório designa o valor ou item que você está procurando. O argumento de matriz look_up obrigatório designa o intervalo de células a serem pesquisadas para este valor de pesquisa, e o argumento return_array designa o intervalo de células contendo o valor que você deseja que seja retornado quando o Excel encontrar uma correspondência exata.

* Lembre-se de que ao designar os argumentos lookup_array e return_array em sua função XLOOKUP, os dois intervalos devem ter o mesmo comprimento, caso contrário, o Excel retornará o #VALUE! erro em sua fórmula. Este é mais o motivo para você usar nomes de intervalo ou nomes de coluna de uma tabela de dados designada ao definir esses argumentos, em vez de apontá-los ou digitar suas referências de célula .

O argumento opcional match_mode pode conter qualquer um dos quatro valores a seguir:

• 0 para uma correspondência exata (o padrão, mesmo quando nenhum argumento match_mode é designado)
• -1 para correspondência exata ou próximo valor menor
• 1 para correspondência exata ou próximo valor maior
• 2 para correspondência parcial usando caracteres curinga unidos à referência de célula no argumento lookup_value

O argumento opcional search_mode pode conter qualquer um dos quatro valores a seguir:

• 1 para pesquisar do primeiro ao último, ou seja, de cima para baixo (o padrão, o mesmo de quando nenhum argumento search_mode é designado)
• -1 para pesquisar do último para o primeiro, ou seja, de baixo para cima
• 2 para uma pesquisa binária em ordem crescente
• -2 para pesquisa binária em ordem decrescente

A melhor maneira de compreender o poder e a versatilidade da nova função XLOOKUP é vê-la em ação em uma planilha do Excel. Na figura a seguir, tenho uma planilha com uma tabela simples de dados de vendas de 2019 organizada por país. Para usar o XLOOKUP para retornar o total de vendas desta tabela na célula E4 com base no país inserido na célula D4 da planilha, siga estas etapas:

Posicione o cursor da célula na célula E4 da planilha

Clique na opção Lookup & Reference na guia Formulas seguido por XLOOKUP próximo à parte inferior do menu suspenso para abrir sua caixa de diálogo Function Arguments.

Clique na célula D4 na planilha para inserir sua referência de célula na caixa de texto de argumento Lookup_value.

Pressione Tab para selecionar a caixa de texto do argumento Lookup_array, clique na célula A4 e mantenha pressionada a tecla Shift enquanto pressiona Ctrl-seta para baixo para selecionar A4: A8 como o intervalo a ser pesquisado (porque o intervalo A3: B8 é definido como uma tabela de dados do Excel, Tabela1 [País] aparece na caixa de texto no lugar do intervalo A4: A8).

Pressione Tab para selecionar a caixa de texto do argumento Return_array, clique na célula B4 e mantenha pressionada a tecla Shift enquanto pressiona Ctrl + seta para baixo para selecionar B4: B8 como o intervalo contendo os valores a serem retornados com base nos resultados da pesquisa (que aparece como Tabela1 [Vendas totais] na caixa de texto).

Clique em OK para inserir a fórmula XLOOKUP na célula E4.

Criação de uma fórmula com XLOOKUP na célula E4 que retorna as vendas com base no país inserido na célula D4.

O Excel insere a fórmula XLOOKUP na célula E4 da planilha e retorna 4900 como resultado porque a Costa Rica está atualmente inserida na célula de pesquisa D4 e, como você pode ver na tabela de vendas de 2019, este é realmente o total de vendas feitas para este país.

Como o XLOOKUP funciona da direita para a esquerda tão bem quanto da esquerda para a direita, você também pode usar essa função para retornar o país desta tabela de vendas com base em um número de vendas específico. A figura a seguir mostra como você faz isso. Desta vez, você cria a fórmula XLOOKUP na célula D4 e designa o valor inserido na célula E4 (11.000, neste caso) como o argumento lookup_value.

Além disso, você insere -1 como o argumento match_mode para substituir o padrão de correspondência exata da função para que o Excel retorne o país com uma correspondência exata para o valor de vendas inserido na célula de pesquisa E4 ou aquele com o próximo menor total de vendas (México com $ 10.000 neste caso, pois não há nenhum país nesta tabela com $ 11.000 de vendas totais). Sem designar um argumento match_mode para esta fórmula, o Excel retornaria #NA como resultado, porque não há correspondência exata para $ 11.000 nesta tabela de vendas.

Criação de uma fórmula com XLOOKUP na célula D4 que retorna o país com base nas vendas inseridas na célula E4

Como a função XLOOKUP é igualmente confortável para pesquisar horizontalmente por coluna e verticalmente por linha, você pode usá-la para criar uma fórmula que executa uma pesquisa bidirecional (substituindo a necessidade de criar uma fórmula que combina as funções INDEX e MATCH como no passado). A figura a seguir, contendo a tabela de programação de produção de 2019 para números de peça, AB-100 a AB-103 para os meses de abril a dezembro, mostra como isso é feito.

Criação de uma fórmula com funções XLOOKUP aninhadas para retornar o número de unidades produzidas para uma peça em um determinado mês

Na célula B12, criei a seguinte fórmula:

= XLOOKUP (part_lookup, $ A $ 3: $ A $ 6, XLOOKUP (date_lookup, $ B $ 2: $ J $ 2, $ B $ 3: $ J $ 6))

Esta fórmula começa definindo uma função XLOOKUP que pesquisa verticalmente por linha por uma correspondência exata para a entrada de peça feita na célula chamada part_lookup (célula B10, neste caso) no intervalo de células $ A $ 3: $ A $ 6 da tabela de produção . Observe, no entanto, que o argumento return_array para esta função LOOKUP original é em si uma segunda função XLOOKUP.

Esta segunda função XLOOKUP aninhada pesquisa o intervalo de células $ B $ 2: $ J $ 2 horizontalmente por coluna para uma correspondência exata com a entrada de data feita na célula chamada date_lookup (célula B11, neste caso). O argumento return_array para esta segunda função XLOOKUP aninhada é $ B $ 3: $ J $ 6, o intervalo de células de todos os valores de produção na tabela.

A forma como essa fórmula funciona é que o Excel primeiro calcula o resultado da segunda função XLOOKUP aninhada, executando uma pesquisa horizontal que, neste caso, retorna a matriz no intervalo de células D3: D6 da coluna Jun-19 (com os valores: 438, 153, 306 e 779) como seu resultado. Este resultado, por sua vez, torna-se o argumento return_array para a função XLOOKUP original que executa uma pesquisa vertical por linha para uma correspondência exata para a entrada do número da peça feita na célula B11 (denominada part_lookup). Como, neste exemplo, esta célula part_lookup contém AB-102, a fórmula retorna apenas o valor de produção Jun-19, 306, do resultado da segunda função XLOOKUP seguinte.

Aí está! Uma primeira olhada em XLOOKUP, uma nova função de pesquisa poderosa, versátil e bastante fácil de usar que pode não apenas fazer pesquisas de valor único realizadas pelas funções VLOOKUP e HLOOKUP, mas também as pesquisas de valor bidirecionais executadas combinando o Funções INDEX e MATCH também.

* Infelizmente, a função XLOOKUP não é compatível com versões anteriores do Microsoft Excel que suportam apenas as funções VLOOKUP e HLOOKUP ou compatível com versões atuais que ainda não a incluem como uma de suas funções de pesquisa, como Excel 2019 e Excel Online . Isso significa que se você compartilhar uma pasta de trabalho contendo fórmulas XLOOKUP com colegas de trabalho ou clientes que usam uma versão do Excel que não inclui essa nova função de pesquisa, todas essas fórmulas retornarão #NOME? valores de erro quando eles abrem sua planilha.