Ir para o conteúdo principal

Função VLOOKUP do Excel

A Função VLOOKUP do Excel é uma ferramenta poderosa que ajuda você a procurar um valor especificado combinando na primeira coluna de uma tabela ou um intervalo verticalmente e, em seguida, retornar um valor correspondente de outra coluna na mesma linha. Embora o VLOOKUP seja incrivelmente útil, às vezes pode ser difícil de entender para iniciantes. Este tutorial visa ajudá-lo a dominar o VLOOKUP, fornecendo explicação passo a passo dos argumentos, exemplos úteis e soluções para erros comuns você pode encontrar ao usar a função VLOOKUP.


Vídeos Relacionados


Explicação passo a passo dos argumentos

Conforme mostrado na captura de tela acima, a função VLOOKUP é usada para encontrar um e-mail com base em um determinado número de ID. Agora fornecerei uma explicação detalhada de como usar VLOOKUP neste exemplo, detalhando cada argumento passo a passo.

Passo 1: Inicie a função VLOOKUP

Selecione uma célula (H6 neste caso) para gerar o resultado e, em seguida, inicie a função VLOOKUP digitando o seguinte conteúdo no Barra de Fórmula.

=VLOOKUP(
Etapa 2: especificar o valor de pesquisa

Primeiro, especifique o valor de pesquisa (que é o que você está procurando) na função VLOOKUP. Aqui, faço referência à célula G6, que contém um determinado número de ID 1005.

=VLOOKUP(G6

Note: o valor de pesquisa deve estar na primeira coluna do intervalo de dados.
Etapa 3: especificar a matriz da tabela

Em seguida, especifique um intervalo de células contendo o valor que você está procurando e o valor que deseja retornar. Neste caso, seleciono o intervalo B6:E12. A fórmula agora aparece da seguinte forma:

=VLOOKUP(G6,B6:E12

Note: Se você deseja copiar a função VLOOKUP para pesquisar vários valores na mesma coluna e obter resultados diferentes, é necessário usar referências absolutas adicionando o cifrão, assim:
=VLOOKUP(G6,$B$6:$E$12
Etapa 4: especifique a coluna da qual deseja retornar um valor

Em seguida, especifique a coluna da qual deseja retornar um valor.

Neste exemplo, como preciso retornar o e-mail com base em um número de ID, aqui insiro um número 4 para dizer ao VLOOKUP para retornar um valor da quarta coluna do intervalo de dados.

=VLOOKUP(G6,B6:E12,4

Etapa 5: encontrar uma correspondência aproximada ou exata

Por fim, determine se você está procurando uma correspondência aproximada ou exata.

  • Para encontrar um correspondência exata, você precisa usar FALSE como último argumento.
  • Para encontrar um correspondência aproximada, usar VERDADEIRO como o último argumento, ou apenas deixe em branco.

Neste exemplo, eu uso FALSE para correspondência exata. A fórmula agora fica assim:

=VLOOKUP(G6,B6:E12,4,FALSE

Pressione a tecla Enter para obter o resultado

Ao explicar cada argumento um por um no exemplo acima, a sintaxe e os argumentos da função VLOOKUP agora são muito mais fáceis de entender.


Sintaxe e argumentos

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

  • Valor_procurado (obrigatório): O valor (um valor real ou uma referência de célula) que você está procurando. Lembre-se que este valor deve estar na primeira coluna do table_array.
  • Tabela_array (obrigatório): Um intervalo de células contém a coluna do valor de pesquisa e a coluna do valor de retorno.
  • Índice_coluna (obrigatório): Um número inteiro representa o número da coluna que contém o valor de retorno. Começa com o número 1 para a coluna mais à esquerda do table_array.
  • Pesquisa de alcance (opcional): um valor lógico que determina se você deseja que VLOOKUP encontre uma correspondência aproximada ou exata.
    • Correspondência aproximada - Defina este argumento como VERDADEIRO, 1 ou deixe-o em branco.
      importante: Para encontrar uma correspondência aproximada, os valores na primeira coluna do table_array devem ser classificados em ordem crescente, caso VLOOKUP retorne o resultado errado.
    • Combinação exata - Defina este argumento como FALSE or 0.

Exemplos

Esta seção demonstra alguns exemplos para ajudá-lo a ter uma compreensão mais abrangente da função VLOOKUP.

Exemplo 1: correspondência exata x correspondência aproximada em VLOOKUP

Se você estiver confuso sobre correspondência exata e correspondência aproximada ao usar VLOOKUP, esta seção pode ajudá-lo a esclarecer essa confusão.

Correspondência exata em VLOOKUP

Neste exemplo, vou encontrar os nomes correspondentes com base nas pontuações listadas no intervalo E6:E8, então insiro a seguinte fórmula na célula F6 e arrasto a alça AutoFill para F8. Nesta fórmula, o último argumento é especificado como FALSE para executar uma pesquisa de correspondência exata.

=VLOOKUP(E6,$B$6:$C$12,2,FALSE)

No entanto, como a pontuação 98 não existe na primeira coluna do intervalo de dados, VLOOKUP retorna o resultado do erro #N/A.

Note: Aqui eu bloqueei o array da tabela ($B$6:$C$12) na função VLOOKUP para referenciar rapidamente um consistente conjunto de dados em vários valores de pesquisa.
Correspondência aproximada em VLOOKUP

Ainda usando o exemplo acima, se você alterar o último argumento para VERDADEIRO, VLOOKUP executará uma pesquisa de correspondência aproximada. Se nenhuma correspondência for encontrada, ele encontrará o próximo maior valor menor que o valor de pesquisa e retornará o resultado correspondente.

=VLOOKUP(E6,$B$6:$C$12,2,TRUE)

Como a pontuação 98 não existe, VLOOKUP localiza o próximo maior valor menor que 98, que é 95, e retorna o nome da pontuação 95 como o resultado mais próximo.

Notas:
  • Nesse caso de correspondência aproximada, os valores na primeira coluna do table_array devem ser classificados em ordem crescente. Caso contrário, VLOOKUP pode não retornar o valor correto.
  • Aqui, bloqueei a matriz da tabela ($B$6:$C$12) na função VLOOKUP para referenciar rapidamente um conjunto consistente de dados em vários valores de pesquisa.

Exemplo 2: Use VLOOKUP com vários critérios

Esta seção demonstra como usar VLOOKUP com várias condições no Excel. Conforme mostrado na captura de tela abaixo, se você estiver tentando localizar um salário com base em um nome fornecido (na célula H5) e departamento (na célula H6), siga as etapas abaixo para fazê-lo.

Etapa 1: adicionar uma coluna auxiliar para concatenar os valores das colunas de pesquisa

Neste caso, precisamos criar uma coluna auxiliar para concatenar os valores do Nome coluna e o Departamento coluna.

  1. Adicione uma coluna auxiliar à esquerda de seu intervalo de dados e dê um cabeçalho a esta coluna. Veja a captura de tela:
  2. Nesta coluna auxiliar, selecione a primeira célula sob o cabeçalho, insira a seguinte fórmula no Barra de Fórmulae pressione Entrar.
    =C6&" "&D6
    Notas: nesta fórmula, usamos um e comercial (&) para unir o texto em duas colunas para produzir um único texto.
    • C6 é o primeiro nome do Nome coluna para juntar, D6 é o primeiro departamento da Departamento coluna para aderir.
    • Os valores dessas duas células são concatenados com um espaço entre eles.
  3. Selecione esta célula de resultado e arraste o Identificador de preenchimento automático para baixo para aplicar esta fórmula a outras células na mesma coluna.
Etapa 2: aplique a função VLOOKUP com os critérios fornecidos

Selecione uma célula onde deseja exibir o resultado (aqui eu seleciono I7), insira a seguinte fórmula no Barra de Fórmula, e depois pressione Entrar.

=VLOOKUP(I5& " "&I6,B6:F12,5,FALSE)
Resultado

Notas:
  • A coluna auxiliar deve ser usada como a primeira coluna do intervalo de dados.
  • Agora a coluna do salário é a quinta coluna do intervalo de dados, então usamos o número 5 como o índice da coluna na fórmula.
  • Precisamos juntar os critérios em I5 e I6 (I5& " "&I6) da mesma forma que a coluna auxiliar e usar o valor concatenado como o lookup_value argumento na fórmula.
  • Você também pode colocar as duas condições diretamente no argumento valor_procurado e separá-las com um espaço (se as condições forem texto, não se esqueça de colocá-las entre aspas duplas).
    =VLOOKUP("Albee IT",B6:F12,5,FALSE)
  • Uma alternativa melhor - pesquisa com vários critérios em segundos
    A Pesquisa de várias condições característica de Kutools for Excel pode ajudá-lo a pesquisar facilmente com vários critérios em segundos. Obtenha uma avaliação gratuita de 30 dias com todos os recursos agora!

Erros e soluções comuns do VLOOKUP

Esta seção lista os erros comuns que você pode encontrar ao usar o VLOOKUP e fornece as soluções para corrigi-los.

  Visão geral dos erros VLOOKUP comuns:
          
         Motivo 1: o valor de pesquisa não está na primeira coluna  
     Motivo 2: o valor de pesquisa não foi encontrado  
  ------  Motivo 3: o valor de pesquisa é menor que o menor valor  
     Motivo 4: os números são formatados como texto  
       Razão 5: Table_array não é constante  
         
  ------  Motivo 1: o valor da pesquisa excede 255 caracteres  
   Razão 2: Col_index é menor que 1  
         
  ------  Motivo 1: Col_index é maior que o número de colunas  
   
         
  ------  Motivo 1: a coluna de pesquisa não está classificada em ordem crescente  
   Motivo 2: uma coluna é inserida ou removida  
         

#N/D erro sendo retornado

O erro mais comum com VLOOKUP é o erro #N/A, o que significa que o Excel não encontrou o valor que você estava procurando. Aqui estão alguns motivos pelos quais VLOOKUP pode retornar o erro #N/A.

Motivo 1: o valor de pesquisa não está na primeira coluna do table_array

Uma das limitações do VLOOKUP do Excel é que ele só permite que você olhe da esquerda para a direita. Portanto, os valores de pesquisa devem estar na primeira coluna do table_array.

Conforme mostrado na captura de tela abaixo, desejo retornar um nome com base no cargo fornecido. Aqui o valor de pesquisa (gerente de vendas) está na segunda coluna do table_array e o valor de retorno está à esquerda da coluna de pesquisa, então VLOOKUP retorna o erro #N/A.

performance

Você pode aplicar qualquer uma das soluções a seguir para corrigir esse erro.

  • Reorganize as colunas
    Você pode reorganizar as colunas para colocar a coluna de pesquisa na primeira coluna do table_array.
  • Use as funções INDEX e MATCH juntas
    Aqui usamos as funções INDEX e MATCH juntas como uma alternativa ao VLOOKUP para resolver esse problema.
    =INDEX(B6:B12,MATCH(F6,C6:C12,0))
  • Use a função XLOOKUP (disponível no Excel 365, Excel 2021 e versões posteriores)
    =XLOOKUP(F6,C6:C12,B6:B12)

Motivo 2: o valor de pesquisa não foi encontrado na coluna de pesquisa (correspondência exata)

Um dos motivos mais comuns pelos quais VLOOKUP retorna o erro #N/A é porque o valor que você está procurando não foi encontrado.

Conforme mostrado no exemplo abaixo, vamos encontrar o nome com base na pontuação dada de 98 em E6. No entanto, essa pontuação não existe na primeira coluna do intervalo de dados, então VLOOKUP retorna o resultado do erro #N/A.

performance

Para corrigir esse erro, você pode tentar uma das seguintes soluções.

  • Se você quiser que o VLOOKUP procure o próximo maior valor que seja menor que o valor de pesquisa, altere o último argumento FALSE (correspondência exata) para VERDADEIRO (correspondência aproximada). Para mais informações, consulte Exemplo 1: correspondência exata x correspondência aproximada usando VLOOKUP.
  • Para evitar alterar o último argumento e obter um lembrete caso o valor de pesquisa não seja encontrado, você pode incluir a função VLOOKUP dentro da função SEERRO:
    =IFERROR(VLOOKUP(E8,$B$6:$C$12,2,FALSE),"Not found")

Motivo 3: o valor de pesquisa é menor que o menor valor na coluna de pesquisa (correspondência aproximada)

Conforme mostrado na captura de tela abaixo, você está realizando uma pesquisa de correspondência aproximada. O valor que você está procurando (o ID número 1001 neste caso) é menor que o menor valor 1002 na coluna de pesquisa, portanto, PROCV retorna erro #N/D.

performance

Aqui estão duas soluções para você.

  • Certifique-se de que o valor de pesquisa seja maior ou igual ao menor valor na coluna de pesquisa.
  • Se você deseja que o Excel o lembre de que o valor pesquisado não foi encontrado, basta aninhar a função VLOOKUP na função SEERRO da seguinte maneira:
    =IFERROR(VLOOKUP(G6,B6:E12,4,TRUE),"Not found")

Motivo 4: os números são formatados como texto

Como você pode ver na captura de tela abaixo, o resultado do erro #N/A neste exemplo é devido a uma incompatibilidade de tipo de dados entre a célula de pesquisa (G6) e a coluna de pesquisa (B6:B12) da tabela original. Aqui, o valor em G6 é um número e os valores no intervalo B6:B12 são números formatados como texto.

Dica: se um número for convertido em texto, um pequeno triângulo verde será exibido no canto superior esquerdo da célula.

performance

Para resolver esse problema, você precisa converter o valor de pesquisa de volta para o número. Aqui estão dois métodos para você.

  • Aplicar o recurso Converter em número
    Clique na célula que deseja converter o texto em número, selecione este botão  ao lado da célula e selecione Converter para número.
  • Aplique uma ferramenta útil para converter em lote entre texto e número
    A Converter entre texto e número característica de Kutools for Excel ajuda você a converter facilmente um intervalo de células de texto em número e vice-versa. Obtenha uma avaliação gratuita de 30 dias com todos os recursos agora!

Razão 5: O table_array não é constante ao arrastar a fórmula VLOOKUP para outras células

Conforme mostrado na captura de tela abaixo, há dois valores de pesquisa em E6 e E7. Depois de obter o primeiro resultado em F6, arraste a fórmula VLOOKUP da célula F6 para F7, um resultado de erro #N/A retornado. Isso ocorre porque as referências de célula (B6:C12) são relativas por padrão e ajustadas à medida que você se move para baixo nas linhas. A matriz da tabela foi movida para B7:C13, que não contém mais a pontuação de pesquisa 73.

Solução

Você precisa bloquear a matriz da tabela para mantê-la constante adicionando um $ assine antes das linhas e colunas nas referências de célula. Para saber mais sobre referência absoluta no Excel, dê uma olhada neste tutorial: Excel referência absoluta (como fazer e usar).

#VALUE erro sendo retornado

As seguintes condições podem fazer com que VLOOKUP retorne o resultado de erro #VALUE.

Motivo 1: o valor de pesquisa excede 255 caracteres

Conforme mostrado na captura de tela abaixo, o valor de pesquisa na célula H4 excede 255 caracteres, então VLOOKUP retorna um resultado de erro #VALUE.

performance

Para contornar essa limitação, você pode aplicar uma função de pesquisa diferente que pode lidar com strings mais longas. Tente uma das seguintes fórmulas.

  • ÍNDICE E CORRESPONDÊNCIA:
    =INDEX(E5:E11, MATCH(TRUE, INDEX(B5:B11=H4, 0), 0))
  • Função XLOOKUP (disponível no Excel 365, Excel 2021 e versões posteriores):
    =XLOOKUP(H4,B5:B11,E5:E11)

Motivo 2: o argumento col_index é menor que 1

O índice da coluna especifica o número da coluna na matriz da tabela que contém o valor que você deseja retornar. Este argumento deve ser um número positivo que corresponda a uma coluna válida na matriz da tabela.

Se você inserir um índice de coluna menor que 1 (ou seja, zero ou negativo), VLOOKUP não conseguirá localizar a coluna na matriz da tabela.

Solução

Para corrigir esse problema, certifique-se de que o argumento do índice da coluna em sua fórmula VLOOKUP seja um número positivo que corresponda a uma coluna válida na matriz da tabela.

#Erro de REF sendo retornado

Esta seção lista um dos motivos pelos quais VLOOKUP retorna o erro #REF e fornece soluções para esse problema.

Razão: O argumento col_index é maior que o número de colunas

Como você pode ver na captura de tela abaixo, a matriz da tabela possui apenas 4 colunas. No entanto, o índice de coluna especificado na fórmula VLOOKUP é 5, que é maior que o número de colunas na matriz da tabela. Como resultado, VLOOKUP não conseguirá localizar a coluna e retornará um erro #REF.

performance

  • Especifique um número de coluna correto
    Certifique-se de que o argumento do índice da coluna em sua fórmula VLOOKUP seja um número que corresponda a uma coluna válida na matriz da tabela.
  • Obtenha automaticamente o número da coluna com base no cabeçalho da coluna especificado
    Se a tabela contiver muitas colunas, você pode ter problemas para determinar o número correto do índice da coluna. Aqui, você pode aninhar a função CORRESP na função VLOOKUP para localizar a posição da coluna com base em um cabeçalho de coluna específico.
    =VLOOKUP(G6,B6:E12,MATCH("Email",B5:E5,0),FALSE)
    Note: Na fórmula acima, o CORRESP("E-mail",B5:E5, 0) função é usada para obter o número da coluna do "E-mail" coluna no intervalo de datas B6:E12. Aqui o resultado é 4, que é usado como col_index na função VLOOKUP.

Valor incorreto sendo devolvido

Se você achar que VLOOKUP não está retornando o resultado correto, isso pode ser causado pelos seguintes motivos

Motivo 1: a coluna de pesquisa não está classificada em ordem crescente

Se você definiu o último argumento como VERDADEIRO (ou deixou vazio) para uma correspondência aproximada e a coluna de pesquisa não estiver classificada em ordem crescente, o valor resultante pode estar incorreto.

Solução

Classificar a coluna de pesquisa em ordem crescente pode ajudá-lo a resolver esse problema. Para fazer isso, siga as etapas abaixo:

  1. Selecione as células de dados na coluna de pesquisa, vá para o Data guia, clique em Classificar do menor para o maior no Classificar e filtrar grupo.
  2. Na série Aviso de classificação caixa de diálogo, selecione o Expanda a seleção opção e clique OK.

Motivo 2: uma coluna é inserida ou removida

Conforme mostrado na captura de tela abaixo, o valor que eu originalmente queria retornar está na quarta coluna da matriz da tabela, então eu especifico o número col_index como 4. Conforme uma nova coluna é inserida, a coluna do resultado torna-se a quinta coluna da tabela array, fazendo com que VLOOKUP retorne o resultado de uma coluna errada.

performance

Aqui estão duas soluções para você.

  • Você pode alterar manualmente o número de índice da coluna para corresponder à posição da coluna de retorno. A fórmula aqui deve ser alterada para:
    =VLOOKUP(H6,B6:F12,5,FALSE)
  • Se você sempre deseja retornar o resultado de uma determinada coluna, como a coluna Email neste exemplo. A fórmula a seguir pode ajudar a corresponder automaticamente o índice da coluna com base no cabeçalho da coluna fornecido, independentemente de as colunas serem inseridas ou removidas da matriz da tabela.
    =VLOOKUP(H6,B6:F12,MATCH("Email",B5:E5,0),FALSE)

Outras notas de função

  • VLOOKUP procura apenas o valor da esquerda para a direita.
    O valor de pesquisa está na coluna mais à esquerda e o valor do resultado deve estar em qualquer coluna à direita da coluna de pesquisa.
  • Se você deixar o último argumento em branco, VLOOKUP usará correspondência aproximada por padrão.
  • VLOOKUP executa uma pesquisa que não diferencia maiúsculas de minúsculas.
  • Para várias correspondências, VLOOKUP retorna apenas a primeira correspondência encontrada na matriz da tabela, com base na ordem das linhas na matriz da tabela.

Melhores ferramentas de produtividade de escritório

🤖 Assistente de IA do Kutools: Revolucionar a análise de dados com base em: Execução Inteligente   |  Gerar Código  |  Crie fórmulas personalizadas  |  Analise dados e gere gráficos  |  Invocar funções do Kutools...
Recursos mais comuns: Encontre, destaque ou identifique duplicatas   |  Excluir linhas em branco   |  Combine colunas ou células sem perder dados   |   Rodada sem Fórmula ...
Super pesquisa: VLookup de múltiplos critérios    VLookup de múltiplos valores  |   VLookup em várias planilhas   |   Pesquisa Difusa ....
Lista suspensa avançada: Crie rapidamente uma lista suspensa   |  Lista suspensa de dependentes   |  Lista suspensa de seleção múltipla ....
Gerenciador de colunas: Adicione um número específico de colunas  |  Mover colunas  |  Alternar status de visibilidade de colunas ocultas  |  Compare intervalos e colunas ...
Recursos em destaque: Foco da Grade   |  Vista de Design   |   Grande Barra de Fórmula    Gerenciador de pastas de trabalho e planilhas   |  Biblioteca (Auto texto)   |  Data Picker   |  Combinar planilhas   |  Criptografar/Descriptografar Células    Enviar e-mails por lista   |  Super Filtro   |   Filtro Especial (filtro negrito/itálico/tachado...) ...
15 principais conjuntos de ferramentas12 Texto Ferramentas (Adicionar texto, Remover Personagens, ...)   |   50+ de cores Tipos (Gráfico de Gantt, ...)   |   Mais de 40 práticos Fórmulas (Calcule a idade com base no aniversário, ...)   |   19 Inclusão Ferramentas (Insira o código QR, Inserir imagem do caminho, ...)   |   12 Conversão Ferramentas (Números para Palavras, Conversão de moedas, ...)   |   7 Unir e dividir Ferramentas (Combinar linhas avançadas, Dividir células, ...)   |   ... e mais

Aprimore suas habilidades de Excel com o Kutools para Excel e experimente uma eficiência como nunca antes. Kutools para Excel oferece mais de 300 recursos avançados para aumentar a produtividade e economizar tempo.  Clique aqui para obter o recurso que você mais precisa...

Descrição


Office Tab traz interface com guias para o Office e torna seu trabalho muito mais fácil

  • Habilite a edição e leitura com guias em Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
  • Abra e crie vários documentos em novas guias da mesma janela, em vez de em novas janelas.
  • Aumenta sua produtividade em 50% e reduz centenas de cliques do mouse para você todos os dias!
Comments (1)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
thank you so much
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations