Como combinar VLOOKUP() com IF() no Excel

Se você gerencia grandes conjuntos de dados no Excel, sabe como é desafiador organizá-los e analisá-los com eficiência. Mas eu tenho uma solução para você: Você pode aninhar a função VLOOKUP() dentro de uma instrução IF() para criar pesquisas dinâmicas com base em condições específicas. Usando essa abordagem, você pode procurar tabelas diferentes com base em uma condição e também pode lidar com erros de forma mais elegante.

Para ser mais específico, o VLOOKUP() encontra pontos de dados específicos em uma tabela, e as instruções do IF() permitem que você tome decisões condicionais com base nesses dados. Ambas as funções são, como sabemos, extremamente importantes. Neste artigo, adicionaremos outra ferramenta à caixa de ferramentas: Você aprenderá como aproveitar o poder dessas duas funções em conjunto para realizar pesquisas condicionais, e as ilustraremos com exemplos práticos.

A resposta rápida: Como combinar VLOOKUP() e IF()

Para criar uma pesquisa condicional, comece com uma instrução IF() e use VLOOKUP() dentro dela para retornar resultados diferentes com base em uma condição. Podemos usar o código a seguir para verificar se um produto está em estoque com base em sua quantidade. Basta você seguir estas etapas:

• Comece com a função IF(): =IF()

• Dentro da função IF(), use VLOOKUP().

=IF(VLOOKUP(C2, $A$2:$B$6, 2, FALSE) > 0, "In Stock", "Out of Stock")Tecnologia

Entendendo as instruções VLOOKUP() e IF() no Excel

VLOOKUP() ajuda você a encontrar dados em uma tabela, enquanto os comandos IF() permitem que você tome decisões com base nesses dados. Juntos, eles ajudam a analisar informações em planilhas. Vamos analisar cada função separadamente e, em seguida, reuni-las. 

O que é VLOOKUP() no Excel?

VLOOKUP() procura um valor específico na primeira coluna de um intervalo e retorna um valor de outra coluna na mesma linha. Aqui está a sintaxe para que você possa usar a fórmula VLOOKUP() para seus grandes conjuntos de dados.

=VLOOKUP(search_key, range, index, is_sorted)Tecnologia

Vamos decompor essa sintaxe e entendê-la:

• Search_key é 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.

• Range define o intervalo de células que contém os dados. A primeira coluna desse intervalo deve conter o endereço search_key. 

• Index é o número da coluna no intervalo do qual você deseja recuperar o valor. A primeira coluna é 1, a segunda é 2 e assim por diante.

• Is_sorted é um valor lógico (TRUE ou FALSE). Você pode usar TRUE (ou 1) para números e FALSE (ou 0) para texto. 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. Se FALSE, VLOOKUP() procura uma correspondência exata. Se o argumento não for especificado, TRUE é o padrão. 

Vamos entender com um exemplo. Aqui tenho uma lista de produtos com seus IDs e preços. Quero encontrar os preços de produtos específicos com base em seu nome.

Lista de produtos com suas IDs e preços. Fonte: Imagem do autor.

Para começar, estou procurando saber especificamente o preço de um tablet. Para isso, insiro um valor search_key (no meu caso, Tablet) para encontrar o preço em qualquer célula. 

Digite o valor para saber o preço dele. Fonte: Imagem do autor.

Em seguida, seleciono uma célula e digito =VLOOKUP().

Digitando a fórmula VLOOKUP(). Fonte: Imagem do autor.

Em seguida, seleciono a célula em que search_key foi inserido.

Selecionando a coluna onde o produto está localizado. Fonte: Imagem do autor.

Em seguida, seleciono o intervalo da tabela. 

Selecionando o intervalo da tabela. Fonte: Imagem do autor.

Contando a partir da esquerda, digito o número de uma coluna da qual quero recuperar os dados. Aqui, quero saber o preço, então digito 2.

Selecionando o índice das colunas. Fonte: Imagem do autor.

Em seguida, digito FALSE para obter a correspondência exata.

Digitando FALSE para a correspondência exata. Fonte: Imagem do autor.

Depois de preencher todos os valores, pressiono Enter:

Usando VLOOKUP(), você obtém o preço do produto. Fonte: Imagem do autor.

Como você pode ver na imagem, o site VLOOKUP() recupera o preço com sucesso.

O que é IF() no Excel?

IF() comparam os valores e os verificam em relação à condição especificada. Aqui está a sintaxe:

=IF(logical_test, [value_if_true], [value_if_false])Tecnologia

Vamos examinar as partes principais para que você possa entendê-las: 

• Logical_test é o valor ou a expressão que você deseja avaliar como TRUE ou FALSE. Essa é a condição que você deseja verificar.

• Value_if_true Retorna o valor se logical_test for TRUE.

• Value_if_false Retorna o valor se logical_test for FALSE.

Vamos dar um exemplo. Aqui, quero fazer comentários para os alunos com base em suas notas. Então, eu preparo a planilha com duas colunas: ALUNOS e GRAUS. 

Uma folha contendo a lista de alunos com suas notas. Fonte: Imagem do autor.

Seleciono uma célula e digito =IF(). Meu objetivo é verificar se os números totais são maiores que 50 e, em seguida, imprimir Excellent, ou, se a nota for menor que 50, imprimo Bad. Depois de especificar as condições, pressiono Enter para obter os resultados.

Atribuir observações a todos os alunos usando a instrução IF. Fonte: Imagem do autor.

Em seguida, copio a fórmula para a última célula preenchida, arrastando-a. Você pode ver os resultados. Atribuí observações a todos os alunos com uma única fórmula. 

Maneiras de combinar VLOOKUP() com IF() no Excel

Vamos dar uma olhada em exemplos práticos para você entender como o VLOOKUP() funciona com o IF().

Pesquisas condicionais

Para criar uma pesquisa condicional, como dissemos, comece com uma instrução IF() e use VLOOKUP() dentro dela para retornar resultados diferentes com base em uma condição.

1. Comece com a função IF(): =IF().

2. Dentro da função IF(), use VLOOKUP().

Vamos tentar um novo exemplo: Aqui eu tenho uma lista de pedidos de produtos com seus tempos de pedido correspondentes. Quero ver se um produto específico foi pedido antes das 12:00 PM.

Uma tabela que contém a lista de produtos junto com o ID e a hora do pedido. Fonte: Imagem do autor.

Primeiro, seleciono uma coluna e insiro a seguinte fórmula: 

=IF(VLOOKUP(A3, A2:C5,3, FALSE) < TIME(12, 0, 0), "Ordered Before Noon", "Ordered After Noon")Tecnologia

Aqui, a função VLOOKUP() procura por Banana na coluna A e retorna o tempo de pedido correspondente da coluna B.

A instrução IF() verifica se o tempo do pedido é menor que 12. Em caso afirmativo, ele retorna Ordered Before Noon. Caso contrário, ele retorna Ordered After Noon.

Cálculo do tempo de entrega combinando as fórmulas IF() e VLOOKUP(). Fonte: Imagem do autor.

Você pode ver como eu rastreio facilmente as entregas dos produtos desejados usando pesquisas condicionais.

Tratamento de erros

Para lidar com erros, comece com uma instrução IF() e use ISNA() com VLOOKUP() dentro dela para verificar se há erros. Por exemplo, para exibir uma mensagem personalizada se um produto não for encontrado:

1. Comece com a função IF(): =IF().

2. Dentro da função IF(), use ISNA() com VLOOKUP() para verificar se há erros.

Vamos esclarecer isso com um exemplo. Digamos que eu tenha uma tabela com preços de produtos e queira exibir uma mensagem personalizada sempre que um produto não for encontrado. 

Uma tabela que contém uma lista de produtos e seus preços. Fonte: Imagem do autor.

Para isso, seleciono uma célula e insiro a seguinte fórmula para encontrar o preço:

=(VLOOKUP(B7, $A$2:$B$5, 2, FALSE)Tecnologia

Em seguida, eu o combino com ISNA() e IF() para lidar com quaisquer erros.

=IF(ISNA(VLOOKUP(B7, $A$2:$B$5, 2, FALSE)), "Product Not Found", (VLOOKUP(B7, $A$2:$B$5, 2, FALSE)))Tecnologia

Tratamento de erros por meio da combinação de fórmulas IF() e ISNA(). Fonte: Imagem do autor.

Aqui, ISNA() verifica se a função VLOOKUP() retorna um erro #N/A, o que aconteceria se não estivesse disponível. Em outras palavras, se ISNA() for avaliado como TRUE, a instrução IF() retornará Product Not Found; caso contrário, ela retornará o preço de VLOOKUP().

Indexação dinâmica de colunas

Para escolher dinamicamente o índice da coluna para VLOOKUP(), comece com uma instrução IF() e use VLOOKUP() dentro dela para selecionar diferentes colunas com base em uma condição. Por exemplo, para procurar colunas diferentes com base em um valor limite:

1. Comece com a função IF(): =IF().

2. Na função IF(), use VLOOKUP() para verificar o limite e selecionar a coluna.

Aqui, tenho uma tabela de produtos com a coluna A contendo os nomes dos produtos, a coluna B contendo os preços dos produtos e a coluna C contendo a quantidade em estoque. Quero procurar o preço do produto ou a quantidade em estoque com base no fato de o preço estar acima ou abaixo de um determinado limite, como 50.

Uma tabela que contém uma lista de produtos, seus IDs e preços. Fonte: Imagem do autor.

Seleciono uma célula e insiro a seguinte fórmula:

=IF(VLOOKUP(B9, $B$9:$D$14, 2, FALSE) > 50, VLOOKUP(B9,$B$9:$D$14, 3, FALSE), VLOOKUP(B9,$B$9:$D$14, 2, FALSE))Tecnologia

Aplicação de IF com VLOOKUP() aninhado. Fonte: Imagem do autor.

Veja como a fórmula funciona: 

• (VLOOKUP(B9, $B$9:$D$14, 2, FALSE) procura o produto em B9 da coluna A e retorna o preço da coluna C.

• A declaração IF() verificou se o preço é maior que $50.

• Se for verdadeiro, nosso código retornará a quantidade em estoque: VLOOKUP(B9,$B$9:$D$14, 3, FALSE).

• Se for falso, nosso código retornará o ID do produto: VLOOKUP(B9,$B$9:$D$14, 2, FALSE)).

Se estiver trabalhando com um conjunto de dados muito grande, você pode copiar a fórmula arrastando-a para a última célula preenchida.

Mostrando resultados. Fonte: Imagem do autor.

E é isso. Como você pode ver, podemos recuperar as informações desejadas com base em condições específicas usando uma fórmula combinada.

Técnicas avançadas usando VLOOKUP() e IF()

Agora que você tem um entendimento básico da combinação de declarações IF() com VLOOKUP(), vamos aprender algumas técnicas avançadas com exemplos que experimentei por conta própria.

Combinação de vários critérios

Se você estiver procurando dados com base em vários critérios, poderá combinar várias funções VLOOKUP() em uma instrução IF() para verificar se todas as condições foram atendidas. 

Aqui eu tenho uma tabela com dados de compra do cliente e status de associação. E quero verificar se um cliente era elegível para um programa de fidelidade, o que requer pelo menos 500 em TOTAL PURCHASE ($) e Gold como MEMBERSHIP STATUS.

Uma tabela que contém os detalhes dos clientes. Fonte: Imagem do autor.

Eu crio outra coluna chamada Elegibilidade, que mostra os critérios de elegibilidade. Em seguida, digito a seguinte fórmula e pressiono Enter: 

=IF(AND(VLOOKUP(B2, $B$2:$D$11, 2, FALSE) >= 500, VLOOKUP(B2, $B$2:$D$11, 3, FALSE) = "Gold"), "Eligible", "Not Eligible")Tecnologia

Verifique os critérios de elegibilidade dos clientes combinando vários critérios. Fonte: Imagem do autor.

Veja como essa fórmula funciona: 

• VLOOKUP(B2, $B$2:$D$11, 2, FALSE) >= 500 verifica se o total de compras do cliente é de pelo menos $500.

• VLOOKUP(B2, $B$2:$D$11, 3, FALSE) = "Gold") verifica se o status de associação de John era Gold.

• AND combinou as condições para garantir que ambas sejam verdadeiras.

• IF() retornou Eligible quando ambas as condições eram verdadeiras. Caso contrário, ele retornou Not Eligible.

Usando VLOOKUP() com IF() para cálculos

Você pode usar VLOOKUP() para encontrar um valor e, em seguida, aplicar uma instrução IF() para realizar cálculos com base nesse valor. 

Aqui eu preparo uma planilha com produtos e seus preços. Depois, quero aplicar um desconto de 10% aos produtos acima de US$ 100.

Uma tabela que contém uma lista de produtos com seus preços. Fonte: Imagem do autor.

Então, crio outra coluna chamada DISCOUNT e escrevo a seguinte fórmula nessa coluna para exibir o desconto.

=IF(VLOOKUP(A2, $A$2:$B$10, 2, FALSE) > 100, VLOOKUP(A2, $A$2:$B$10, 2, FALSE) * 0.9, VLOOKUP(A2, $A$2:$B$10, 2, FALSE))Tecnologia

Agora, veja como essa fórmula funciona:

• VLOOKUP(A2, $A$2:$B$10, 2, FALSE) recuperou o preço do produto, que é 56.

• =IF(VLOOKUP(A2, $A$2:$B$10, 2, FALSE) > 100, ..., ...) verificado se o preço do produto era maior que 100. 

• Como a condição é verdadeira, o site VLOOKUP(A2, $A$2:$B$10, 2, FALSE) * 0.9 aplicou um desconto de 10%. 

• (VLOOKUP(A2, $A$2:$B$10, 2, FALSE)) imprimiu os preços reais do produto em que a condição de desconto não era verdadeira.

Foi assim que obtive o resultado final da fórmula do produto, que foi 135. Para obter os resultados desejados para todos os produtos, você pode arrastar a fórmula para baixo e copiá-la para a última célula preenchida.

Usando VLOOKUP() com IF para calcular os descontos em produtos cujos preços são maiores que 100. Fonte: Imagem do autor.

Manuseio de grandes conjuntos de dados

Você também pode combinar o site VLOOKUP() com a função IF() para otimizar a recuperação de dados e o tratamento de erros ao trabalhar com grandes conjuntos de dados.

Aqui está nosso exemplo final: Tenho uma planilha com informações de funcionários e quero recuperar o departamento de um funcionário e lidar com casos em que o ID do funcionário não existe.

Uma tabela que contém uma lista de funcionários com suas IDs e departamentos. Fonte: Imagem do autor.

Então, selecionei uma célula e digitei a seguinte fórmula: 

=IFERROR(VLOOKUP(E3, $A$2:$C$18, 3, FALSE), "Not Found")Tecnologia

Depois de pressionar Enter, arrasto a fórmula para copiá-la também para outras células.

Recuperação do departamento do funcionário por meio do ID e tratamento de erros se o funcionário não for encontrado. Fonte: Imagem do autor.

E você pode ver os resultados acima. Veja como essa fórmula funciona: 

• O site VLOOKUP(E3, $A$2:$C$18, 3, FALSE) recupera o departamento do funcionário.

• IFERROR trata os erros. Em vez de mostrar uma mensagem de erro (#N/A), você verá uma mensagem amigável e personalizada.

Simplificando, você não precisa mais se aprofundar em planilhas. Porque quando você combina os demonstrativos VLOOKUP() e IF(), é possível lidar facilmente com os maiores conjuntos de dados no Excel. 

Considerações finais

Ao combinar o site VLOOKUP() com as declarações do IF(), você pode criar planilhas mais precisas e resistentes a erros. Não deixe de experimentar os exemplos que compartilhei para ver como essas técnicas podem simplificar as tarefas de gerenciamento de dados e aprimorar suas habilidades no Excel.

Se você quiser aprimorar suas habilidades, confira nosso curso Introdução ao Excel e, em seguida, atualize para o programa de habilidades Fundamentos do Excel para dominar o básico. Quando você se sentir confortável com essas funções, aprimore suas habilidades analíticas com nosso curso Análise de dados no Excel. Mas se você estiver mais voltado para o lado financeiro, confira nosso curso Modelagem financeira no Excel para integrar insights financeiros. 

Além disso, nosso curso Preparação de dados no Excel ajudará você a simplificar o processo de limpeza de dados, e o curso Visualização de dados no Excel ajudará você a apresentar seus dados de forma atraente.