Função VLOOKUP do Excel

A função VLOOKUP do Excel é uma ferramenta poderosa que o ajuda a procurar um valor especificado, correspondendo à primeira coluna de uma tabela ou intervalo de forma vertical e, em seguida, a devolver um valor correspondente de outra coluna na mesma linha. Embora a função VLOOKUP seja extremamente útil, por vezes pode ser difícil de compreender para principiantes. Este tutorial tem como objetivo ajudá-lo a dominar a função VLOOKUP, fornecendo uma explicação passo a passo dos argumentos, exemplos úteis e soluções para erros comuns que poderá encontrar ao utilizar a função VLOOKUP.

• Explicação passo a passo dos argumentos
• Sintaxe e argumentos
• Exemplos de VLOOKUP
  Correspondência exata vs. correspondência aproximada | VLOOKUP com múltiplas condições
• Erros comuns do VLOOKUP e respetivas soluções
  Erro #N/D|Erro #VALOR|Erro #REF|Valor incorreto

Exemplos de VLOOKUP.xlsx Erros de VLOOKUP.xlsx

Vídeos Relacionados

Explicação passo a passo dos argumentos

Conforme ilustrado na captura de ecrã acima, a função VLOOKUP é utilizada para localizar um endereço de correio eletrónico com base num número de identificação específico. Vou agora explicar, passo a passo, como utilizar a função VLOOKUP neste exemplo, analisando cada um dos seus argumentos em detalhe.

Passo 1: Iniciar a função VLOOKUP

Selecione uma célula (neste caso, a H6) para apresentar o resultado e inicie a função VLOOKUP digitando o seguinte conteúdo na Barra de Fórmulas.

=VLOOKUP(

Passo 2: Especificar o valor de procura

Em primeiro lugar, especifique o valor de pesquisa (ou seja, aquilo que está a procurar) na função VLOOKUP. Aqui, faço referência à célula G6, que contém o número de identificação 1005.

=VLOOKUP(G6

Passo 3: Especificar a matriz de tabela

De seguida, especifique um intervalo de células que inclua tanto o valor que está a procurar como o valor que pretende devolver. Neste caso, selecionei o intervalo B6:E12. A fórmula apresenta-se agora da seguinte forma:

=VLOOKUP(G6,B6:E12

=VLOOKUP(G6,$B$6:$E$12

Passo 4: Especificar a coluna da qual pretende devolver um valor

Depois, indique a coluna da qual pretende obter um valor.

Neste exemplo, como preciso de obter o endereço de correio eletrónico com base num número de identificação, insiro o número 4 para indicar à função VLOOKUP que deve devolver o valor da quarta coluna do intervalo de dados.

=VLOOKUP(G6,B6:E12,4

Passo 5: Procurar uma correspondência aproximada ou exata

Por fim, escolha se pretende uma correspondência aproximada ou exata.

• Para encontrar uma correspondência exata, tem de utilizar FALSO como último argumento.
• Para encontrar uma correspondência aproximada, utilize VERDADEIRO como último argumento ou deixe-o em branco.

Neste exemplo, utilizo FALSO para garantir uma correspondência exata. A fórmula apresenta-se agora da seguinte forma:

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

Prima a tecla Enter para obter o resultado

Ao explicar cada argumento individualmente no exemplo acima, a sintaxe e os argumentos da função VLOOKUP tornam-se muito mais claros e fáceis de compreender.

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 está a procurar. Lembre-se de que este valor tem de estar na primeira coluna da matriz_tabela.
• Matriz_tabela (obrigatório): um intervalo de células que contém tanto a coluna do valor procurado como a coluna do valor de retorno.
• Índice_col (obrigatório): um número inteiro que indica a coluna contendo o valor de retorno. A contagem começa em 1, correspondendo à coluna mais à esquerda da matriz_tabela.
• Procura_intervalo(opcional): Um valor lógico que determina se pretende que o VLOOKUP encontre uma correspondência aproximada ou uma correspondência 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 da matriz_tabela devem estar ordenados por ordem crescente, caso contrário, o PROCV poderá devolver um resultado incorreto.
  • Correspondência exata – Defina este argumento como FALSO ou 0.

Exemplos

Esta secção apresenta exemplos práticos que o ajudam a compreender de forma mais abrangente a função VLOOKUP.

Exemplo 1: Correspondência exata versus correspondência aproximada na função VLOOKUP

Se estiver confuso sobre a diferença entre correspondência exata e correspondência aproximada ao utilizar a função VLOOKUP, esta secção ajudá-lo-á a esclarecer essa dúvida.

Correspondência exata na função VLOOKUP

Neste exemplo, vou procurar os nomes correspondentes com base nas pontuações listadas no intervalo E6:E8, pelo que introduzo a seguinte fórmula na célula F6 e arrasto a alça de autorreenchimento até F8. Nesta fórmula, o último argumento é definido como FALSO para efetuar uma procura com correspondência exata.

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

Contudo, como a pontuação 98 não está presente na primeira coluna do Intervalo de Dados, a função VLOOKUP devolve o erro #N/D.

Correspondência aproximada na função VLOOKUP

Continuando com o exemplo anterior, se alterar o último argumento para VERDADEIRO, a função VLOOKUP realizará uma procura com correspondência aproximada. Caso não seja encontrada uma correspondência exata, a função procurará o maior valor inferior ao valor de procura e devolverá o resultado correspondente.

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

Como a pontuação 98 não existe, a função VLOOKUP encontra o maior valor inferior a 98 — ou seja, 95 — e devolve o nome correspondente a essa pontuação como o resultado mais próximo.

Exemplo 2: Utilizar a função VLOOKUP com múltiplos critérios

Esta secção mostra como utilizar a função VLOOKUP com múltiplas condições no Excel. Conforme ilustrado na captura de ecrã abaixo, se pretender encontrar um salário com base num nome (na célula H5) e num departamento (na célula H6), siga os passos indicados para o conseguir.

Passo 1: Adicionar uma coluna auxiliar para concatenar os valores das colunas de procura

Neste caso, é necessário criar uma coluna auxiliar para concatenar os valores da coluna Nome e da coluna Departamento.

1. Adicione uma coluna auxiliar à esquerda do seu intervalo de dados e atribua-lhe um cabeçalho. Veja a imagem:
   
   
2. Nesta coluna auxiliar, selecione a primeira célula abaixo do cabeçalho, introduza a seguinte fórmula na Barra de Fórmulas e prima Enter.
   

   =C6&," "&,D6

   
   Notas: Nesta fórmula, utilizamos um ampersand (&,) para juntar o texto de duas colunas e criar um único fragmento de texto.

   • C6 é o primeiro nome da coluna Nome a juntar, D6 é o primeiro departamento da coluna Departamento a juntar.
   • Os valores destas duas células são concatenados, com um espaço entre eles.
3. Selecione esta célula de resultado e arraste a alça de preenchimento automático para baixo, de modo a aplicar esta fórmula às restantes células da mesma coluna.
   
   

Passo 2: Aplicar a função VLOOKUP com os critérios fornecidos

Selecione uma célula onde pretenda apresentar o resultado (neste caso, selecione I7), introduza a seguinte fórmula na Barra de Fórmulas e, em seguida, prima Enter.

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

Resultado

Erros comuns da função VLOOKUP e respetivas soluções

Esta secção apresenta os erros mais comuns que poderá encontrar ao utilizar a função VLOOKUP e oferece soluções práticas para os corrigir.

Erro #N/D devolvido

O erro mais comum com a função VLOOKUP é o #N/D, que indica que o Excel não encontrou o valor procurado. Eis algumas razões pelas quais a função VLOOKUP pode devolver este erro.

Razão 1: O valor de procura não está na primeira coluna da matriz_tabela

Uma das limitações da função VLOOKUP do Excel é que apenas permite procurar Da Esquerda para a Direita. Assim, o Intervalo de valor de pesquisa deve estar na primeira coluna da matriz_tabela.

Conforme mostrado na captura de ecrã abaixo, pretendo obter um nome com base no cargo fornecido. Neste caso, o valor de procura ()gestor de vendas) encontra-se na segunda coluna da matriz_tabela, e o valor a devolver está à esquerda da coluna de procura — por isso, a função VLOOKUP devolve o erro #N/D.

Soluções

Pode aplicar qualquer uma das seguintes soluções para corrigir este erro.

• Reorganize as colunas
  
  Pode reorganizar as colunas de forma a colocar a coluna de procura na primeira coluna da matriz_tabela.
• Utilize as funções ÍNDICE e CORRESP juntas
  
  Aqui, utilizamos as funções ÍNDICE e CORRESP em conjunto como uma alternativa ao VLOOKUP para resolver este problema.

  =INDEX(B6:B12,MATCH(F6,C6:C12,0))

  
• Utilize a função PROCX (disponível no Excel 365, Excel 2021 e versões posteriores)
  

  =XLOOKUP(F6,C6:C12,B6:B12)

Razão 2: The lookup value is not found in the lookup column (exact match)

Uma das razões mais comuns para a função VLOOKUP devolver o erro #N/D é o valor procurado não ter sido encontrado.

Conforme ilustrado no exemplo abaixo, pretendemos encontrar o nome correspondente à pontuação de 98 indicada em E6. No entanto, como essa pontuação não existe na primeira coluna do Intervalo de Dados, a função VLOOKUP devolve o erro #N/D.

Soluções

Para corrigir este erro, pode tentar uma das seguintes soluções.

• Se pretender que o VLOOKUP procure o maior valor inferior ao valor procurado, altere o último argumento FALSO (correspondência exata) para VERDADEIRO (correspondência aproximada). Para mais informações, consulte Exemplo 1: Correspondência exata vs. correspondência aproximada com VLOOKUP.
• Para evitar alterar o último argumento e obter um lembrete caso o valor procurado não seja encontrado, pode envolver a função VLOOKUP na função SEERRO:
  

  =IFERROR(VLOOKUP(E8,$B$6:$C$12,2,FALSE),"Not found")

Razão 3: The lookup value is smaller than the smallest value in the lookup column (approximate match)

Conforme ilustrado na captura de ecrã abaixo, está a realizar uma procura com correspondência aproximada. Como o valor que procura (neste caso, o número de identificação 1001) é inferior ao menor valor da coluna de procura (1002), a função VLOOKUP devolve o erro #N/D.

Soluções

Eis duas soluções pensadas para si.

• Certifique-se de que o valor procurado seja o menor da coluna de pesquisa.
• Se pretender que o Excel o avise de que o valor procurado não foi encontrado, basta inserir a função VLOOKUP na função SEERRO da seguinte forma:
  

  =IFERROR(VLOOKUP(G6,B6:E12,4,TRUE),"Not found")

Razão 4: Os números estão formatados como texto

Conforme pode observar na captura de ecrã abaixo, o erro #N/D neste exemplo deve-se a uma incompatibilidade de tipo de dados entre a célula de procura (G6) and the lookup column (B6:B12) da tabela original. Aqui, o valor em G6 é um número, enquanto os valores no intervalo B6:B12 são números formatados como texto.

Soluções

Para resolver este problema, é necessário converter o valor de procura novamente em número. Eis dois métodos para si.

• Aplique a funcionalidade Converter em Número
  
  Clique na célula que pretende converter o Texto para valor, selecione este botão ao lado da célula e, em seguida, selecione Converter em Número.
  
• Aplique uma ferramenta prática para converter em lote Conversão entre texto e valor
  
  A funcionalidade Conversão entre texto e valordo Kutools for ExcelAjuda-o a converter facilmente um intervalo de células de texto em valor e vice-versa.Obtenha já a sua avaliação gratuita de 30 dias com todas as funcionalidades!

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

Como se mostra na captura de ecrã abaixo, existem dois Intervalo de valor de pesquisa em E6 e E7. Após obter o primeiro resultado em F6, ao arrastar a fórmula VLOOKUP da célula F6 para F7, é devolvido um erro #N/D. Isto acontece porque as referências de células (B6:C12) são relativas por predefinição e ajustam-se à medida que avança pelas linhas. A matriz da tabela foi deslocada para B7:C13, que já não contém o valor de procura 73.

Solução

Tem de bloquear a matriz da tabela para a manter constante, adicionando um $ antes das linhas e colunas nas referências de células. Para saber mais sobre referências absolutas no Excel, consulte este tutorial: Referência absoluta no Excel (como criar e utilizar).

Erro #VALOR! a ser devolvido

As seguintes condições podem provocar o erro #VALOR! na função VLOOKUP.

Motivo 1: O valor de procura excede os 255 caracteres

Como se pode ver na captura de ecrã abaixo, o valor de procura na célula H4 excede os 255 caracteres, pelo que a função VLOOKUP devolve o erro #VALOR!.

Soluções

Para contornar esta limitação, pode aplicar uma função de procura diferente, capaz de lidar com cadeias mais longas. Experimente uma das seguintes fórmulas.

• ÍNDICE e CORRESP:
  

  =INDEX(E5:E11, MATCH(TRUE, INDEX(B5:B11=H4, 0), 0))

  
• Função PROCX(disponível no Excel 365, Excel 2021 e versões posteriores):
  

  =XLOOKUP(H4,B5:B11,E5:E11)

Motivo 2: O argumento índice_col é inferior a 1

O índice de coluna especifica o número da coluna na matriz da tabela que contém o valor que pretende devolver. Este argumento tem de ser um número positivo correspondente a uma coluna válida na matriz da tabela.

Se introduzir um índice de coluna inferior a 1 (ou seja, zero ou negativo), a função VLOOKUP não conseguirá encontrar a coluna na matriz da tabela.

Solução

Para resolver este problema, certifique-se de que o argumento índice_col na sua fórmula VLOOKUP seja um número positivo correspondente a uma coluna válida na matriz da tabela.

Erro #REF! a ser devolvido

Esta secção explica uma das razões pelas quais a função VLOOKUP devolve o erro #REF! e apresenta soluções para resolver este problema.

Motivo: O argumento índice_col é superior ao número de colunas

Como pode verificar na captura de ecrã abaixo, a matriz da tabela tem apenas 4 colunas. Contudo, o índice de coluna especificado na fórmula VLOOKUP é 5, valor superior ao número de colunas da matriz da tabela. Consequentemente, a função VLOOKUP não conseguirá localizar a coluna e devolverá um erro #REF!.

Soluções

• Especifique um número de coluna correto
  Certifique-se de que o argumento índice_col na sua fórmula VLOOKUP é um número que corresponda a uma coluna válida na matriz_tabela.
• Obtenha automaticamente o número da coluna com base no cabeçalho especificado
  Se a sua tabela tiver muitas colunas, pode ser difícil identificar manualmente o índice correto da coluna. Neste caso, basta combinar a função CORRESP com a função VLOOKUP para localizar automaticamente a posição da coluna com base no respetivo cabeçalho.

  =VLOOKUP(G6,B6:E12,MATCH("Email",B5:E5,0),FALSE)

  Nota: Na fórmula acima, a função MATCH(«Email»,B5:E5,0) obtém o número da coluna correspondente ao cabeçalho "Email" no intervalo B5:E5. O resultado é 4, que é utilizado como o argumento índice_col na função VLOOKUP.

Valor incorreto a ser devolvido

Se verificar que a função VLOOKUP não está a devolver o resultado correto, tal poderá dever-se às seguintes razões

Motivo 1: A coluna de procura não está ordenada por ordem crescente

Se definir o último argumento como VERDADEIRO(ou)o deixar em branco) para uma correspondência aproximada, e a coluna de pesquisa não estiver ordenada por ordem crescente, o resultado poderá estar incorreto.

Solução

Ordenar a coluna de procura por ordem crescente pode ajudá-lo a resolver este problema. Para tal, siga os passos abaixo:

1. Selecione as células de dados na coluna de procura, aceda ao separador Dados, clique em Ordenar Do menor para o maior no grupo Ordenar e Filtrar.
2. Na caixa de diálogo Aviso de Ordenação, selecione a opção Expandir a seleção e clique em OK.

Motivo 2: Uma coluna foi inserida ou removida

Conforme se pode ver na captura de ecrã abaixo, o valor que originalmente pretendia obter encontrava-se na quarta coluna da matriz da tabela, pelo que defini o número do índice_col como 4. Com a inserção de uma nova coluna, a coluna com os resultados passou a ser a quinta da matriz, levando a função VLOOKUP a devolver dados de uma coluna incorreta.

Soluções

Eis duas soluções para si.

• Pode alterar manualmente o número do índice da coluna para corresponder à posição do Coluna de retorno. A fórmula aqui deve ser alterada para:

  =VLOOKUP(H6,B6:F12,5,FALSE)

• Se pretender sempre devolver o resultado de uma determinada coluna, como a coluna Email neste exemplo, a seguinte fórmula pode ajudá-lo a corresponder automaticamente o índice da coluna com base no respetivo cabeçalho, independentemente de colunas serem inseridas ou removidas da matriz_tabela.

  =VLOOKUP(H6,B6:F12,MATCH("Email",B5:E5,0),FALSE)

Outras notas sobre funções

• O VLOOKUP procura apenas valores da esquerda para a direita.
  O valor procurado deve estar na coluna mais à esquerda, e o valor do resultado pode estar em qualquer coluna à direita dessa coluna de procura.
• Se deixar o último argumento em branco, o VLOOKUP utiliza por defeito a correspondência aproximada.
• O VLOOKUP realiza uma procura sem distinção entre maiúsculas e minúsculas.
• Em caso de múltiplas correspondências, o VLOOKUP devolve apenas a primeira correspondência encontrada na matriz_tabela, com base na ordem das linhas na matriz_tabela.

Artigos Relacionados