Como fazer um VLOOKUP() com vários critérios

VLOOKUP() é uma das funções mais usadas do Excel. Ele permite que você procure e recupere dados de uma coluna específica em uma tabela. Além disso, ele ajuda a mesclar dados de diferentes planilhas ou a localizar informações relacionadas.

Mas você sabia que a funcionalidade do VLOOKUP()pode ser ampliada para incluir vários critérios? Isso ajudará os usuários que frequentemente precisam combinar várias condições ao pesquisar dados, o que é um problema comum que aparece ao trabalhar com planilhas. 

Neste artigo, veremos como você pode usar o site VLOOKUP() com vários critérios para elevar o nível do seu trabalho. Se você é novo no Excel, confira primeiro o nosso curso Introdução ao Excel para aprender os prós e contras do gerenciamento de tabelas e da aplicação de cálculos.

Noções básicas de VLOOKUP()

Antes de entrarmos no site VLOOKUP() com vários critérios, vamos primeiro revisar a sintaxe básica do site VLOOKUP(), que é a seguinte: 

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Na fórmula acima:

• lookup_value é o valor que você deseja pesquisar. Pode ser um número, um texto ou uma referência a uma célula que contenha o valor da pesquisa.

• table_array é o intervalo de células que contém os dados. A primeira coluna desse intervalo deve conter a chave de pesquisa.

• col_index_num é o número da coluna no intervalo do qual você deseja recuperar o valor. 

• range_lookup é onde você insere um valor lógico (TRUE ou FALSE). Se você usar TRUE, VLOOKUP() presumirá que a primeira coluna do intervalo está classificada em ordem crescente e retornará a correspondência mais próxima.

Vamos entender isso com um exemplo. Aqui, tenho uma planilha cheia de dados relacionados a funcionários e quero encontrar rapidamente o nome de um funcionário específico com base em seu número de identificação. Para isso, usarei o site VLOOKUP(). 

Uma tabela que contém detalhes do funcionário. Fonte: Imagem do autor.

Primeiro, insiro um valor na célula F6 que corresponde a um valor na tabela. No meu caso, estou vendo a coluna de referência EMP ID quando seleciono 4032.

Inserindo o valor VLOOKUP() em uma célula. Fonte: Imagem do autor.

Em seguida, seleciono a célula G6 e começo a digitar minha fórmula.

Inserção da fórmula VLOOKUP() em uma célula. Fonte: Imagem do autor.

Em seguida, seleciono a célula em que digitei 4032. Este é o valor que estou vendo na tabela. 

Inserir o número da célula em que o valor VLOOKUP() está armazenado. Fonte: Imagem do autor. 

Em seguida, seleciono o intervalo da tabela (A2:D11). 

Selecionando o intervalo da tabela que contém os dados. Fonte: Imagem do autor.

Contando a partir da esquerda, seleciono a coluna número 2 porque estou procurando FIRST NAME e FIRST NAME é a segunda coluna acima. 

Inserir o número da coluna para a qual você deseja obter resultados. Fonte: Imagem do autor.

Depois de digitar FALSE para obter uma correspondência exata, pressiono Enter. Você pode ver como a função VLOOKUP() encontra o FIRST NAME do funcionário com EMP ID igual a 4032.

Recuperar a saída desejada. Fonte: Imagem do autor.

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

VLOOKUP()O critério de pesquisa, em sua forma básica, funciona bem para pesquisas simples, mas pode se tornar um desafio quando você combina vários critérios. Mas você pode lidar com essas limitações com algumas técnicas. Vamos entender como. 

VLOOKUP() com uma coluna auxiliar 

Se você precisar usar o site VLOOKUP() com vários critérios, poderá usar uma coluna auxiliar para combinar vários critérios em um único critério. Vamos ilustrar isso com o exemplo a seguir, em que quero encontrar o valor das vendas de cada pessoa com base em uma data e em um produto. 

Uma tabela que contém detalhes do cliente. Fonte: Imagem do autor.

Para começar, crio uma nova coluna concatenando os vários critérios em um identificador exclusivo. No meu caso, três colunas serão concatenadas: Customer Name, Product, e Date.

Criar uma coluna "auxiliar". Fonte: Imagem do autor.

Aqui está a fórmula que uso para combinar as colunas em uma só:

=B2&"|"&C2&"|"&D2

Como você pode ver, há um símbolo de pipe após o nome da coluna. Isso funciona como um separador entre os dados de cada coluna e geralmente é considerado uma boa prática ao concatenar.

Concatenar três colunas em uma célula. Fonte: Imagem do autor.

Se você estiver se perguntando por que há um número em vez de uma data, embora tenhamos selecionado uma coluna de data, isso ocorre porque o formato da data foi alterado para um formato numérico. A pesquisa funcionará da mesma forma, mas se você quiser ver os dados em uma forma mais reconhecível, use a função TEXT(). A função TEXT() recebe dois parâmetros: value é o valor que você deseja alterar e format_text é o formato para o qual você deseja alterar o valor.

Fórmula de texto. Fonte: Imagem do autor.

Portanto, para concatenar e também formatar a data, digito o seguinte:

=B2&"|"&C2&"|"&TEXT(D2, "DD-MM-YY")

Formatação da data usando a fórmula TEXT(). Fonte: Imagem do autor.

Em seguida, arrasto a fórmula para baixo para preencher a nova coluna em todas as linhas.

Copiar a fórmula arrastando-a. Fonte: Imagem do autor.

E você está pronto. Todas as três colunas são concatenadas com sucesso. Em seguida, preparo a tabela de pesquisa, que você pode ver na imagem abaixo. 

Preparando minha tabela. Fonte: Imagem do autor.

Em seguida, digito a seguinte fórmula e pressiono Enter. Você pode ver os resultados abaixo. 

=VLOOKUP(G6&"|"&H6&"|"&TEXT(I6, "DD-MM-YY"),$A$2:$E$11, 5,0)

Recuperar as vendas usando o Vlookup com vários critérios. Fonte: Imagem do autor.

VLOOKUP() com a função CHOOSE() 

A função CHOOSE() seleciona e retorna um valor de uma lista com base em um índice ou posição especificada. Esta é a sintaxe da função CHOOSE():

CHOOSE(index_num, value1, [value2], ...)

Nessa fórmula:

• index_num é um número que especifica qual valor você deve escolher.

• value1, value2, … é a lista de valores que você pode escolher.

A função CHOOSE() pode ajudar quando você precisa procurar valores com base em vários critérios ou quando a estrutura de dados não se encaixa perfeitamente em uma única tabela. Nesses casos, o CHOOSE() criará uma tabela virtual para combinar colunas de diferentes fontes ou reordenar as colunas de acordo com as necessidades de pesquisa que você tiver. Vamos entender isso com um exemplo. Tenho o seguinte conjunto de dados: 

Uma tabela contendo as pontuações dos alunos em três trimestres. Fonte: Imagem do autor.

Agora, quero obter as pontuações dos alunos no primeiro trimestre. Então, insiro a seguinte fórmula na célula F2:

=VLOOKUP($E2&"|"&F$1, CHOOSE({1,2}, $A$2:$A$16&"|"&$B$2:$B$16, C2:C15), 2, FALSE)

Obtenha as pontuações dos alunos do primeiro trimestre. Fonte: Imagem do autor.

Veja como a fórmula funciona:

• CHOOSE({1,2}, $A$2:$A$16&"|"&$B$2:$B$16, C2:C15) cria uma coluna auxiliar virtual mesclando vários critérios em identificadores exclusivos. 

• =VLOOKUP($E2&"|"&F$1, ..., 2, FALSE) procura o valor especificado nessa primeira coluna virtual e recupera a pontuação associada da segunda coluna.

Dessa forma, você também pode encontrar o que precisa sem criar colunas extras na planilha.

Uma alternativa para VLOOKUP() com vários critérios

Embora VLOOKUP() seja uma função amplamente utilizada no Excel, ela não é a única função para procurar dados. Você pode explorar alternativas como INDEX() e MATCH(). Vamos ver como eles trabalham juntos para atender ao mesmo propósito de VLOOKUP() com vários critérios.

INDEX() e MATCH() como VLOOKUP()

As funções INDEX() e MATCH() usadas em conjunto permitem pesquisas dinâmicas de colunas. Você pode usá-los para trabalhar com dados não classificados em ordem crescente, e eles também podem lidar com pesquisas horizontais e verticais. Vamos mostrar isso com o conjunto de dados a seguir:

Uma tabela que contém dados de funcionários. Fonte: Imagem do autor.

Com base nesses dados, quero descobrir a posição de John, que tem 25 anos e mora em Chicago. 

Criando uma tabela de referência. Fonte: Imagem do autor.

Agora, digitamos a seguinte fórmula no G5:

=INDEX(C2:C5,MATCH(1,(G3=A2:A5)*(G4=B2:B5)*(G6=D2:D5),0))

Usando INDEX() e MATCH() juntos. Fonte: Imagem do autor.

Considerações finais

Abordei algumas das técnicas mais importantes que podem ser usadas com VLOOKUP()- colunas auxiliares, fórmulas de matriz e até mesmo funções sofisticadas como INDEX() e MATCH(). Esses métodos facilitam o manuseio de grandes conjuntos de dados e deixam você mais confiante em seus dados.

Se você quiser saber mais sobre análise e visualização de dados no Excel, confira estes cursos: Análise de dados no Excel e Visualização de dados no Excel. Também temos uma leitura mais curta, que é o nosso tutorial Visualizando dados no Excel.