Como comparar duas colunas no Excel usando VLOOKUP

Ao trabalhar com grandes conjuntos de dados no Excel, pode ser necessário comparar duas colunas para encontrar semelhanças ou diferenças.

VLOOKUP é uma função poderosa que permite pesquisar dados correspondentes entre duas colunas. A função permite pesquisar valores em uma coluna que aparecem em outra.

Este artigo mostra como comparar duas colunas usando VLOOKUP, para que você possa analisar seus dados com eficiência.

Índice

• Fundamentos da função Vlookup
  • Função Vlookup
• Preparando seus dados para VLOOKUP
  • Formatando os dados
• Como usar VLOOKUP para comparar duas colunas
  • Tratamento de outros erros
• Alternativas ao VLOOKUP para comparação de colunas
  • 1. Usando funções de índice e correspondência
  • 2. Empregando XLOOKUP no Excel
• Cinco dicas para melhorar o desempenho do VLOOKUP
  • 1. Limite seu intervalo de pesquisa
  • 2. Use referências absolutas
  • 3. Classifique seus dados
  • 4. Use um VLOOKUP duplo
  • 5. Use formatação condicional
• Usos avançados para VLOOKUP
• Nossa palavra final

Fundamentos da função Vlookup

Suponha que você tenha uma planilha com duas listas de itens na coluna A e na coluna B. Você deseja localizar os itens da Lista 1 que também aparecem na Lista 2.

Você pode imaginar que trabalhar manualmente em listas de milhares de itens consumiria muito tempo. Felizmente, o Excel traz VLOOKUP para o resgate!

O termo VLOOKUP significa pesquisa vertical. A função irá comparar duas colunas, encontrar correspondências entre elas e retornar os valores associados.

Função Vlookup

Aqui está uma sintaxe básica da função VLOOKUP:

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

Esta é uma divisão dos elementos:

• lookup_value : o valor que você deseja procurar na primeira coluna do table_array.

• table_array : o intervalo de células que contém os dados que você deseja pesquisar.

• col_index_num : o número da coluna no table_array do qual você deseja retornar o valor.

• range_lookup : Opcional. É VERDADEIRO (correspondência aproximada) ou FALSO (correspondência exata). O padrão é verdadeiro.

Preparando seus dados para VLOOKUP

Antes de usar VLOOKUP para comparar duas colunas no Excel , você precisa preparar seus dados.

Crie duas colunas separadas em sua planilha onde deseja comparar os valores. Este artigo usa a Coluna A e a Coluna B para nossos exemplos.

Aqui estão os dados de exemplo usados ​​neste artigo:

Esta é a tabela de amostra com cabeçalho a ser adicionada à seção intitulada “Preparando seus dados para VLOOKUP”:

Lista 1 Lista 2
Item 1 Item 3
Item 2 Item 4
Item 3 Item 1
Item 4 Item 5

Formatando os dados

É importante garantir que os dados em ambas as colunas sejam formatados de maneira semelhante. VLOOKUP diferencia maiúsculas de minúsculas, o que significa que maiúsculas e minúsculas são importantes.

Erros de correspondência podem ocorrer no resultado final se a formatação for inconsistente.

Também é uma boa ideia remover quaisquer valores duplicados ou células em branco para minimizar o risco de erros.

Para remover valores duplicados:

1. Selecione a coluna.

2. Vá para a guia Dados na faixa de opções superior.

3. Clique em Remover Duplicatas na seção Ferramentas de Dados.

O botão pode ser um pouco difícil de ver. Esta imagem irá ajudá-lo:

Para remover células em branco:

1. Selecione a coluna.

2. Vá para a guia Início na faixa de opções superior.

3. Expanda o menu "Localizar e selecionar".

4. Escolha “Ir para Especial”.

5. Selecione “Em branco” nas opções.

6. Clique em "Excluir" na seção Células.

Como usar VLOOKUP para comparar duas colunas

Depois de preparar os dados, você pode escrever a fórmula VLOOKUP para comparar duas colunas no Excel e identificar correspondências. Siga esses passos:

1. Selecione uma célula em uma nova coluna onde deseja exibir os resultados da comparação (por exemplo, célula C2).

2. Digite a seguinte fórmula: =PROCV(A2, B:B, 1, FALSO))

3. Pressione Enter para aplicar a fórmula.

4. No caso de um valor correspondente, o valor será exibido na mesma linha da coluna de resultado (por exemplo, Coluna C).

5. Arraste a fórmula para baixo de C2 para copiá-la em quantas células você precisar.

Se estiver usando nossos dados de amostra, coloque os resultados da comparação na terceira coluna. A fórmula encontrará três correspondências dos quatro itens da Lista 1.

Observe que o Item 2 está sendo exibido como #N/A. O Excel está nos informando que a correspondência não é aplicável, ou seja, não foi possível encontrá-la.

Isso está correto e é uma informação útil sobre valores ausentes. No entanto, alguns usuários do Excel podem pensar que há algum problema de dados ou erro de função.

É uma boa prática mostrar uma indicação diferente de que nenhuma correspondência foi encontrada. Isso poderia ser simplesmente um espaço em branco.

Para fazer isso, combine a função VLOOKUP com a função IFNA assim:

=SEFNA(PROCV(A1, B:B, 1, FALSO), “”)

A função IFNA detecta o erro #N/A e substitui a saída por um espaço vazio (“”). Você também pode usar a função ISNA ou ter lógica adicional com a função IF.

Tratamento de outros erros

Às vezes, VLOOKUP pode gerar outros erros em seus resultados de comparação. A #REF! Erro no Excel é um problema genuíno com seus dados.

Geralmente ocorre quando o intervalo especificado está incorreto. Nosso exemplo referenciou a coluna inteira, mas você também pode usar intervalos de células verticais.

Certifique-se de que o intervalo de pesquisa ao qual você está se referindo abrange todos os valores que deseja comparar.

Alternativas ao VLOOKUP para comparação de colunas

Existem duas principais funções alternativas de pesquisa ao comparar duas colunas no Excel para encontrar correspondências.

1. Usando funções de índice e correspondência

Em vez de usar VLOOKUP, você pode comparar duas colunas no Excel combinando as funções ÍNDICE e CORRESP.

Este método fornece uma maneira mais flexível de pesquisar dados e é particularmente útil ao trabalhar com colunas não adjacentes ou quando o índice da coluna pode mudar.

Esta é a sintaxe para colocar na coluna de resultado:

=ÍNDICE(return_range, MATCH(lookup_value, lookup_range, 0))

• Return_range : o intervalo de células que contém os dados que você deseja retornar.

• Lookup_value : o valor que você deseja pesquisar dentro do intervalo de pesquisa.

• Lookup_range : o intervalo de células dentro do qual você deseja encontrar o valor de pesquisa.

Usando os mesmos dados dos exemplos anteriores, substituímos a fórmula VLOOKUP da seguinte forma:

=(ÍNDICE(B:B, CORRESPONDÊNCIA(A2, B:B, 0)))

A função MATCH retorna a posição relativa do valor de pesquisa dentro do intervalo de pesquisa e a função INDEX retorna o valor correspondente do intervalo de retorno.

Os resultados nos mesmos dados serão os mesmos do VLOOKUP que usamos anteriormente. Esta imagem mostra as funções em uso:

Você também pode substituir o erro #N/A por uma mensagem personalizada. Aqui está um exemplo de uso da função SEERRO.

=SEERRO(ÍNDICE(B:B, CORRESP(A2, B:B, 0)), “Não encontrado”)

2. Empregando XLOOKUP no Excel

Para usuários com Excel 365 ou Excel 2019, o XLOOKUP é um teste lógico alternativo ao VLOOKUP para encontrar valores comuns em duas colunas.

XLOOKUP oferece várias vantagens. Você pode usá-lo para pesquisar dados horizontal e verticalmente, trabalhar com colunas não adjacentes e especificar valores personalizados para erros.

A sintaxe de XLOOKUP é:

=XLOOKUP(lookup_value, lookup_range, return_range, [if_not_found], [match_mode], [search_mode])

• lookup_value : o valor que você deseja pesquisar dentro do intervalo de pesquisa.

• intervalo_de_pesquisa : o intervalo de células dentro do qual você deseja encontrar o valor de pesquisa.

• return_range : o intervalo de células que contém os dados que você deseja retornar.

• Adicione valores de erro personalizados, modo de correspondência e parâmetros de modo de pesquisa conforme necessário.

XLOOKUP encontrará o valor de pesquisa dentro do intervalo de pesquisa e retornará o valor correspondente do intervalo de retorno.

A fórmula é particularmente útil quando você tem duas listas incorporadas em conjuntos de dados de várias colunas. Em nossos exemplos anteriores, as listas estavam na primeira coluna e na segunda coluna, mas sua planilha pode ter mais dados do que isso.

Aqui está um exemplo com duas listas na primeira e na terceira coluna de uma planilha:

A fórmula XLOOKUP se parece com isso:

=XLOOKUP(A2,C:C,C:C,””)

Esta figura mostra o resultado com a comparação na coluna D. O primeiro valor está presente, mas o segundo está ausente.

Observe que não tenho fórmulas de erro extras, mas o valor ausente é exibido em branco. Isso ocorre porque estou usando o quarto parâmetro, que é um valor de erro personalizado. Neste caso, é uma string em branco.

Como bônus, mostrarei como usar o XLOOKUP para comparar as linhas. Se você tiver duas listas na linha 1 e na linha 2, o uso mais simples da fórmula será assim:

=XLOOKUP(B1, 2:2, 2:2, “”)

Esta imagem mostra os resultados nas duas linhas.

Com sua funcionalidade e flexibilidade aprimoradas, o XLOOKUP é uma excelente alternativa ao VLOOKUP para comparar listas no Excel para determinar se há correspondências.

Cinco dicas para melhorar o desempenho do VLOOKUP

A otimização de sua função VLOOKUP pode ajudá-lo a evitar longos tempos de espera e melhorar a capacidade de resposta geral de sua planilha do Excel.

Aqui estão seis dicas e truques que você pode aplicar para melhorar o desempenho do seu VLOOKUP no Excel.

1. Limite seu intervalo de pesquisa

Usei colunas inteiras nos exemplos para torná-los simples. Se você estiver trabalhando com uma grande quantidade de dados, evite fazê-lo. O uso de colunas completas pode retardar o processo de cálculo do Excel.

Em vez disso, tente usar o intervalo exato necessário para seus dados (por exemplo, A1:A100). Isso reduz o número de células que sua função VLOOKUP precisa avaliar.

2. Use referências absolutas

Ao especificar um intervalo de dados (por exemplo, as células de B2 a B5), use referências absolutas. Isso garante que a fórmula seja consistente e que os dados existam quando você a copia em várias células.

Aqui está um exemplo:

=PROCV(A2, $B$2:$B$5, 1, FALSO)

3. Classifique seus dados

Se você sabe que os dados na coluna de pesquisa estão classificados em ordem crescente, você pode usar VLOOKUP com TRUE ou 1 para o argumento 'range_lookup'.

Isso fará com que o Excel execute uma correspondência aproximada, que é mais rápida do que uma correspondência exata em todas as células. No entanto, tenha cuidado ao usar esta opção, pois uma classificação incorreta pode levar a resultados incorretos.

4. Use um VLOOKUP duplo

Você pode usar duas funções VLOOKUP para acelerar o processo de pesquisa no Excel.

O primeiro VLOOKUP determinará se o valor de pesquisa existe definindo 'col_index_num' como 1 e 'range_lookup' como TRUE.

Se retornar TRUE, um segundo VLOOKUP recupera o valor desejado com 'range_lookup' definido como TRUE.

5. Use formatação condicional

Você pode usar a formatação condicional em sua planilha do Excel para destacar valores correspondentes ou ausentes na coluna especificada. Você também pode aplicar cores a valores exclusivos. Isso facilita a leitura dos seus dados.

Você encontrará o menu de formatação condicional no grupo de estilos na guia Início.

Usos avançados para VLOOKUP

Os testes T de amostras pareadas são usados ​​para comparar as médias de duas amostras relacionadas. Este vídeo mostra seu uso no Excel.

Se você tiver dados ou variáveis ​​adicionais que precisa referenciar durante sua análise, você pode usar VLOOKUP para recuperar os valores necessários de outra tabela ou planilha.

Por exemplo, você pode usar VLOOKUP para recuperar informações demográficas ou condições de tratamento para cada observação pareada em seu conjunto de dados.

Nossa palavra final

Até agora, você tem uma sólida compreensão do uso do VLOOKUP. Esta poderosa função permite identificar rapidamente diferenças e valores correspondentes entre duas listas, tornando a análise de dados mais eficiente e precisa.

Você também viu exemplos de outras funções de pesquisa e referência, como XLOOKUP, INDEX e MATCH. Adicionar esses elementos às suas habilidades fortalecerá ainda mais seus recursos de análise de dados.

Continue praticando e refinando suas habilidades de VLOOKUP e logo você se tornará um especialista em comparar colunas no Excel, economizando tempo e aumentando sua produtividade.