Como usar XLOOKUP() com vários critérios no Excel

XLOOKUP() no Excel é, atualmente, a função mais recente do grupo de funções de busca (LOOKUP(), VLOOKUP(), HLOOKUP() e XLOOKUP()) e oferece muitas vantagens, recursos ampliados e mais flexibilidade.

Neste tutorial, primeiro vamos falar para que serve a função XLOOKUP() e por que ela é melhor que as funções de busca mais antigas; depois, veremos sua sintaxe básica e, em seguida, vamos ao que interessa: como usar XLOOKUP() com vários critérios.

Se você precisa aprender os fundamentos do Excel, o curso para iniciantes Introduction to Excel é o ponto de partida ideal.

Por que usar XLOOKUP() no Excel

A função XLOOKUP() pesquisa um intervalo ou matriz de dados e retorna o item correspondente à primeira correspondência. Se nenhuma correspondência for encontrada, XLOOKUP() pode retornar uma correspondência aproximada, caso um tipo de correspondência específico seja definido. Em muitos aspectos, XLOOKUP() supera suas antecessoras no Excel (VLOOKUP(), HLOOKUP() e LOOKUP()).

Em especial, ela permite:

• pesquisar dados na horizontal e na vertical, em qualquer direção
• usar múltiplos critérios de busca
• retornar uma correspondência aproximada, mantendo a exata como padrão
• fazer correspondência parcial
• retornar múltiplas colunas e linhas
• retornar um texto personalizado quando nada é encontrado.

Além disso, XLOOKUP() é mais rápida que as funções de busca antigas, o que faz diferença ao pesquisar em grandes volumes de dados.

Como usar XLOOKUP() com uma condição

Vamos ver rapidamente a sintaxe básica de XLOOKUP():

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

em que os parâmetros obrigatórios são:

• lookup_value—o valor a ser procurado

• lookup_array—o intervalo ou matriz onde buscar

• return_array—o intervalo ou matriz de retorno

e os opcionais são:

• [if_not_found]—o texto a retornar quando não houver correspondência

• [match_mode]—o tipo de correspondência (exata ou aproximada) e o que retornar quando não encontrar.

• [search_mode]—o modo de pesquisa (direta ou reversa, busca binária em matriz ordenada crescente ou decrescente).

Veremos exemplos dos parâmetros opcionais mais adiante.

Para aprender ou revisar outras funções e fórmulas úteis no Excel, confira o tutorial The 15 Basic Excel Formulas Everyone Needs to Know e a Excel Formulas Cheat Sheet.

Como usar XLOOKUP() com vários critérios

Pela sintaxe básica, a função XLOOKUP() foi feita para trabalhar, por padrão, com um único valor de pesquisa de uma variável específica.

Mas é possível adaptá-la para atuar sobre várias variáveis ao mesmo tempo, buscando um valor diferente em cada variável. Em outras palavras, dá para pesquisar com XLOOKUP() aplicando múltiplos critérios. Para isso, há duas abordagens principais: concatenação e expressões booleanas. Vamos ver as duas.

Antes de entrar nos detalhes, observe a tabela do Excel que usaremos. Ela traz dados de 10 gatos, incluindo nomes, cores e idades:

Nos exemplos, vamos usar XLOOKUP() para encontrar uma das três características de um gato usando as outras duas.

Tudo bem a tabela ser simples e dar para achar tudo visualmente. Aqui, o objetivo é entender os princípios de como usar XLOOKUP() com múltiplos critérios. Dominando esses princípios, você consegue extrapolar para cenários reais mais complexos, por exemplo, com mais de dois critérios ou exigências específicas.

XLOOKUP() com vários critérios usando concatenação

A abordagem por concatenação é bem direta: concatenamos os valores de busca e os arrays correspondentes entre si. Veja como funciona.

Suponha que queremos achar a idade de uma gata branca e cinza chamada Nala. Para facilitar, colocamos esses dois critérios na mesma planilha da tabela:

Nesse caso, a fórmula XLOOKUP() fica assim:

=XLOOKUP(G2&G3, B2:B11&C2:C11, D2:D11)

Ela vai retornar 2.5, a idade da primeira Nala branca e cinza (minha gata 😺). Lembre-se: XLOOKUP() retorna o item da primeira correspondência.

Para entender o que são os dois primeiros componentes da fórmula—os com e-comercial—podemos executá-los em células separadas:

=B2:B11&C2:C11

O resultado é NalaWhite e Grey.

=B2:B11&C2:C11

O resultado é:

Sim, é isso mesmo: só concatenamos os valores de busca e os arrays de busca, seguindo a sintaxe básica de XLOOKUP() que vimos.

XLOOKUP() com vários critérios usando expressões booleanas

Agora, vamos fazer a mesma tarefa—achar a idade de uma Nala branca e cinza—usando a segunda abordagem: expressões booleanas. A fórmula XLOOKUP() fica assim:

=XLOOKUP(1, (B2:B11=G2)*(C2:C11=G3), D2:D11)

Como esperado, também retorna 2.5, a idade da primeira Nala branca e cinza.

Vamos destrinchar os componentes. Aqui, 1 significa TRUE, ou seja, procuramos o valor TRUE no array de busca representado pelo segundo componente—(B2:B11=G2)*(C2:C11=G3).

Esse componente contém dois multiplicadores, cada um testando uma condição: o primeiro verifica se o nome corresponde ao que buscamos; o segundo, se a cor corresponde. Vamos executar cada multiplicador separadamente:

=B2:B11=G2

O resultado é:

=C2:C11=G3

O resultado é:

Agora, vamos executar o segundo componente completo:

=(B2:B11=G2)*(C2:C11=G3)

O resultado é:

Embora vejamos dois valores 1 no resultado (ou seja, dois TRUE), XLOOKUP() retorna o item da primeira correspondência.

A abordagem com expressões booleanas para múltiplos critérios parece mais complexa e menos intuitiva do que a de concatenação. E ficaria ainda pior com mais de dois critérios. Por que, então, não usar sempre concatenação?

Porque as expressões booleanas dão muito mais flexibilidade ao usar XLOOKUP() com múltiplos critérios. Veja as vantagens.

Verificando o valor FALSE

Na fórmula anterior, checamos se a expressão booleana era TRUE, passando o valor 1:

=XLOOKUP(1, (B2:B11=G2)*(C2:C11=G3), D2:D11)

Em alguns casos, pode ser necessário verificar se a expressão é FALSE. Por exemplo, queremos a idade do primeiro gato da tabela que não é uma Nala branca e cinza. A fórmula fica:

=XLOOKUP(0, (B2:B11=G2)*(C2:C11=G3), D2:D11)

O resultado será 1, a idade do primeiro gato que não é uma Nala branca e cinza (na verdade, é uma Nala tricolor, não branca e cinza).

Usando operadores lógicos

Com expressões booleanas, não ficamos limitados a testar apenas igualdade. Digamos que queremos achar a cor de uma Nala com menos de 2 anos:

A fórmula XLOOKUP() será:

=XLOOKUP(1, (B2:B11=G2)*(D2:D11<G3), C2:C11)

O resultado será Tricolor.

Atender a pelo menos um critério

Até aqui, estávamos checando o atendimento a todos os critérios. Em outros cenários, pode ser preciso atender a pelo menos um critério.

Voltando à tarefa inicial—achar a idade de uma Nala branca e cinza:

Desta vez, queremos a idade de um gato que seja Nala ou branco e cinza. A fórmula fica:

=XLOOKUP(1, (B2:B11=G2)+(C2:C11=G3), D2:D11)

Ela retorna 1, a idade de uma Nala tricolor.

Relembrando: quando buscávamos a idade de um gato que fosse Nala e branco e cinza, a fórmula era:

=XLOOKUP(1, (B2:B11=G2)*(C2:C11=G3), D2:D11)

Trocar * por + fez toda a diferença.

Se quiser explorar todo o potencial do Excel, considere a trilha completa, prática e equilibrada Excel Fundamentals.

Parâmetros opcionais de XLOOKUP() com múltiplos critérios

Assim como no uso com um único critério, também podemos aproveitar as opções extras de XLOOKUP() quando rodamos com múltiplos critérios. É aqui que entram os parâmetros opcionais [if_not_found], [match_mode] e [search_mode].

Vamos ver exemplos rápidos de cada um com múltiplos critérios. Para simplificar, usaremos a abordagem por concatenação.

Retornar um texto personalizado quando nada for encontrado

Aqui estamos procurando a idade de uma Nala preta—um registro inexistente na tabela:

Nesse caso, adicionamos à fórmula XLOOKUP() o parâmetro opcional [if_not_found], com o texto a ser retornado se não houver correspondência:

=XLOOKUP(G2&G3, B2:B11&C2:C11, D2:D11, "No cat is found")

De fato, a função retornou o texto definido: No cat is found. Se não informássemos nada, o retorno seria #N/A.

Retornar uma correspondência aproximada

Digamos que procuramos a idade de uma Nala tricolor, mas não temos certeza se na tabela está "Tricolor" ou "Tricolour". Nesse caso, precisamos de uma correspondência aproximada com curingas e passar o argumento [match_mode] igual a 2.

Na tabela acima, usamos o asterisco (*), que representa qualquer número de caracteres, inclusive 0. Outros curingas estão na documentação da Microsoft.

A fórmula XLOOKUP() será:

=XLOOKUP(G2&G3, B2:B11&C2:C11, D2:D11, , 2)

E retornará 1, a idade da primeira (e única) Nala tricolor da tabela.

Observe que o argumento 2 na fórmula indica que queremos uma correspondência aproximada, e não buscar literalmente uma Nala da cor "Tricolo*r". No nosso caso, o asterisco capturou "Tricolor", mas faria o mesmo com "Tricolour" ou até "Tricolooor".

Fazer uma pesquisa reversa

Vamos repetir o exercício do início—achar a idade de uma Nala branca e cinza—, mas agora começando pelo último item.

Para isso, adicionamos o parâmetro opcional [search_mode] com valor -1, assim:

=XLOOKUP(G2&G3, B2:B11&C2:C11, D2:D11, , , -1)

A função retorna 7, a idade da primeira Nala branca e cinza a partir do fim da matriz de busca.

XLOOKUP() vs. INDEX() e MATCH()

Em versões antigas do Excel, para replicar algo semelhante ao uso de XLOOKUP() com múltiplos critérios, era preciso combinar INDEX() e MATCH(). Sem entrar a fundo na sintaxe, veja como encontrar a idade da primeira Nala branca e cinza com o método antigo (INDEX() + MATCH()) e com o novo XLOOKUP() (as referências de célula estão na tabela anterior):

=INDEX(D2:D11, MATCH(1, (G2=B2:B11)*(G3=C2:C11), 0))
=XLOOKUP(1, (B2:B11=G2)*(C2:C11=G3), D2:D11)

Embora os argumentos pareçam semelhantes, destacam-se as principais vantagens de usar XLOOKUP() em vez do método antigo:

• Basta uma única função — não é necessário combinar funções.

• Possibilidade de aplicar a abordagem por concatenação, quando fizer sentido.

• Uso de parâmetros opcionais (a função MATCH() não os oferece).