Uso da função VLOOKUP e exemplos específicos

A função VLOOKUP do Excel é extremamente útil na pesquisa de dados. Aqui está o que você precisa saber sobre como usar a função VLOOKUP no Excel .

Você deseja verificar um valor específico em uma planilha do Excel e procurar informações sobre ele. Executar um VLOOKUP economiza um esforço significativo neste caso. É uma das melhores maneiras de executar uma consulta vertical no Microsoft Excel. O artigo explicará o significado, uso e exemplos ilustrativos da função VLOOKUP no Microsoft Excel.

• Estas são as funções mais básicas do Excel que você precisa entender

Como usar a função VLOOKUP

• Qual é a função PROCV?
• Fórmula da função VLOOKUP no Excel 2016, 2013, 2010, 2007, 2003
• Tutorial em vídeo sobre como usar a função VLOOKUP
• Exemplo 1: Use a função VLOOKUP para avaliar as avaliações de alunos e funcionários:
• Exemplo 2: Use VLOOKUP para detecção absoluta para obter dados
• Exemplo 3: Use VLOOKUP para extrair dados
• Exemplo 4: Use a função VLOOKUP em 2 planilhas Excel diferentes
• Para que usar a função VLOOKUP?
• Coisas para lembrar sobre a função VLOOKUP

Qual é a função PROCV?

A função VLOOKUP é uma função de pesquisa de dados no Excel. Presumo que você tenha experiência em Excel e possa usar funções básicas como SUM, AVERAGE e TODAY. Uma das funções mais comuns do VLOOKUP é uma função de dados, o que significa que opera em tabelas de banco de dados ou, mais simplesmente, em listas de itens. A lista tem muita variedade. Você pode criar uma tabela sobre o pessoal da sua empresa, categorias de produtos, listas de clientes ou qualquer outra coisa. E abaixo está uma lista de produtos que a empresa A está vendendo no mercado:

As tabelas do banco de dados geralmente possuem identificadores para cada produto. Na tabela de dados acima, o sinal de identificação especial é a coluna “Código do Item”. Nota: Para poder utilizar a função VLOOKUP, a tabela de dados deve ter uma coluna contendo sinais de identificação como código ou ID, e deve ser a primeira coluna como a tabela acima.

VLOOKUP é provavelmente a função mais famosa do Excel, mas por boas e más razões. Pelo lado positivo, a função VLOOKUP é fácil de usar e faz algo muito útil. Especialmente para novos usuários, observar a função VLOOKUP escaneando a tabela, encontrando uma correspondência e retornando um resultado preciso é extremamente emocionante. O uso proficiente da função VLOOKUP é uma habilidade essencial, desde iniciantes até usuários proficientes do Excel.

Do lado negativo, a função VLOOKUP é limitada e possui valores padrão perigosos. Ao contrário de INDEX e MATCH (ou XLOOKUP), a função VLOOKUP precisa de uma tabela completa com valores de pesquisa na primeira coluna. Isso dificulta o uso da função VLOOKUP com vários critérios. Além disso, o comportamento de correspondência padrão do VLOOKUP facilita a obtenção de resultados incorretos. Mas não se preocupe! A chave para usar com sucesso a função VLOOKUP é dominar o básico.

VLOOKUP tem 4 argumentos: lookup_value, table_array, column_index_num e range_lookup. Lookup_value é o valor a ser procurado e table_array é o intervalo de dados vertical para pesquisar. A primeira coluna de table_array deve conter os valores de pesquisa a serem pesquisados. O argumento column_index_num é o número da coluna do valor a ser recuperado, em que a primeira coluna de table_array é a coluna 1.

Finalmente, range_lookup controla o comportamento de encontrar uma correspondência. Se range_lookup for TRUE, a função VLOOKUP realizará uma correspondência aproximada. Se range_lookup for FALSE, a função VLOOKUP realizará uma correspondência exata. É importante ressaltar que range_lookup é opcional e o padrão é TRUE, então VLOOKUP realizará uma correspondência aproximada por padrão.

Fórmula da função VLOOKUP no Excel

A função VLOOKUP no Excel tem a seguinte fórmula:

=PROCV(valor_procurado,matriz_tabela,núm_índice_coluna ,[procura_intervalo] )

Lá:

• VLOOKUP: é o nome da função
• Os parâmetros em negrito são obrigatórios.
• lookup_value: valor usado para pesquisa
• table_array: Tabela contendo o valor a ser pesquisado, colocado em valor absoluto com sinal $ na frente, por exemplo: $A$3:$E$40.
• col_index_num: Ordem da coluna que contém o valor de pesquisa em table_array. Por exemplo, na tabela $A$3:$E$40, a coluna B contém o valor a ser pesquisado, então col_index_num aqui será 2; tabela $C$3:$F$40, a coluna E contém o valor de pesquisa, então col_index_num aqui é 3.
• range_lookup: É o intervalo de pesquisa, TRUE é equivalente a 1 (pesquisa relativa), FALSE é equivalente a 0 (pesquisa absoluta). Este parâmetro nem sempre é obrigatório na fórmula.

A pesquisa relativa só pode ser aplicada quando os valores a serem pesquisados ​​em table_array foram organizados em ordem (crescente ou decrescente ou em ordem alfabética). Com essas tabelas você pode usar a pesquisa relativa, que é semelhante ao uso de uma função SE infinita . Para valores que não podem ser pesquisados ​​ou não classificados, use a pesquisa absoluta para encontrar o valor exato.

Tutorial em vídeo sobre como usar a função VLOOKUP

Exemplo 1: função VLOOKUP para avaliar as avaliações de alunos e funcionários

Suponha que você tenha um histórico escolar do seguinte modo:

E a tabela de regulação do ranking é a seguinte:

Agora usaremos a função VLOOKUP para inserir as avaliações dos alunos. Você notará que a tabela de classificação foi organizada de baixo para alto (de fraco para bom), portanto, neste caso, podemos usar a pesquisa relativa.

No Excel essas 2 tabelas são apresentadas da seguinte forma:

Aqui, o valor a ser detectado está na coluna C, começando na linha 6. O intervalo de pesquisa é $A$18:$B$21, a ordem da coluna que contém o valor detectado é 2.

Na célula D6, insira a fórmula: =VLOOKUP($C6,$A$18:$B$21,2,1). Esta é uma fórmula de pesquisa relativa, você pode realizar uma pesquisa absoluta se quiser (ou porque a classificação não está classificada) adicionando 0 à fórmula como esta: =VLOOKUP($C6 ,$A$18:$B$21,2, 0). Pressione Enter.

Clique na célula D6, um pequeno quadrado aparece no canto inferior direito, clique nele e arraste para baixo na tabela para copiar a fórmula de notas para os demais alunos.

Nesse momento, temos os resultados do uso da função VLOOKUP para classificar o desempenho acadêmico dos alunos da seguinte forma:

Você está se perguntando por que $ deve ser usado antes de C6? $ manterá a coluna C fixa, alterando as linhas apenas quando você arrastar a fórmula para baixo na tabela. Há também $A$18:$B$21 para ajudar a corrigir a tabela de regras de classificação, fazendo com que ela não mude quando você arrasta a fórmula.

Exemplo 2: A função VLOOKUP pesquisa absolutamente para obter dados

Suponha que você tenha uma tabela de dados de funcionários, armazenando código de funcionário, nome completo e cargo. Outra tabela armazena código do funcionário, cidade natal e nível de escolaridade. Agora que você deseja preencher as informações da cidade natal e o nível de escolaridade de cada funcionário, o que deve fazer?

Suponha que você tenha uma tabela de funcionários e cidades de origem dos funcionários da seguinte forma:

Agora você deseja preencher as informações da cidade natal dos funcionários. Na célula D4, insira a fórmula de pesquisa absoluta da seguinte forma: =Vlookup($A4,$A$16:$C$25,2,0)

Em seguida, pressione Enter. Clique no pequeno quadrado que aparece no canto da célula D4 e arraste-o para baixo na tabela para copiar a fórmula para outros funcionários.

Para preencher as informações de qualificação dos funcionários, na célula E4, insira a fórmula de pesquisa absoluta: =VLOOKUP($A4,$A$16:$C$25,3,0)

Se você continuar pressionando Enter e rolar para baixo para copiar a fórmula para os funcionários restantes, obteremos o seguinte resultado:

Exemplo 3: Use VLOOKUP para extrair dados

Continuando com o conjunto de dados do exemplo 2, encontraremos agora as cidades natais de 3 funcionários: Nguyen Hoang Anh, Tran Van Anh e Nguyen Quang Vinh. Extraí-o para uma nova tabela F15:G18.

Farei essa busca na tabela A3:E13, após preencher minha cidade natal e qualificações. Agora, insira a seguinte fórmula de pesquisa absoluta na célula G16: =VLOOKUP($F16,$B$3:$E$13,3,0) > pressione Enter.

Copiando a fórmula para os 2 funcionários restantes obtemos o seguinte resultado:

Observe que neste exemplo, o valor de detecção está na coluna B, portanto a área de detecção é calculada a partir da coluna B e não da coluna A.

Exemplo 4: Use a função VLOOKUP em 2 planilhas Excel diferentes

Voltando ao conjunto de dados do exemplo 2, após o preenchimento da qualificação do funcionário e da cidade de origem, denominamos a planilha de QTM.

Em outra planilha da planilha, denominada QTM1, é necessário obter informações sobre as qualificações e cargo do funcionário com a ordem de contratação do funcionário alterada. É quando você vê o verdadeiro poder da função VLOOKUP.

Para encontrar dados sobre as “Qualificações” do funcionário, insira a fórmula: =VLOOKUP($B4,QTM!$B$3:$E$13,4,0) na célula C4 da planilha QTM1.

Lá:

• B4 é a coluna que contém o valor usado para detecção.
• QTM! é o nome da planilha que contém a tabela com o valor a ser detectado. Após o nome da planilha, adicione um !
• $B$3:$E$13 é a tabela que contém os valores de pesquisa e a planilha que contém a tabela (QTM).
• 4 é o número de série da coluna “Qualificações”, calculado a partir da coluna “Nome completo” da planilha QTM.
• 0 é detecção absoluta.

Pressione Enter e copie a fórmula para todos os funcionários restantes da tabela, obtemos o seguinte resultado:

Para encontrar os dados de “Cargo” do funcionário, na célula D4 da planilha QTM1, insira a fórmula: =VLOOKUP($B4,QTM!$B$3:$E$13,2,0), pressione Enter.

Copiando a fórmula para os demais funcionários, obtemos o seguinte:

Para que usar a função VLOOKUP?

Primeiro precisamos encontrar a resposta exata para a pergunta “para que usamos a função VLOOKUP?”.

A função VLOOKUP é usada para oferecer suporte à pesquisa de informações em um campo de dados ou lista com base nos identificadores disponíveis.

Por exemplo, inserir a função VLOOKUP com o código de um produto em outra planilha exibirá as informações do produto correspondentes a esse código. Essas informações podem ser descrição, preço, quantidade em estoque, dependendo do conteúdo da receita que você escreve.

Quanto menor for a quantidade de informações que você precisa encontrar, mais difícil será escrever a função VLOOKUP. Normalmente você usaria essa função em uma planilha reutilizável como modelo. Cada vez que o código do produto apropriado for inserido, o sistema recuperará todas as informações necessárias sobre o produto correspondente.

Coisas para lembrar sobre a função VLOOKUP

Aqui está uma lista de coisas importantes a serem lembradas sobre a função VLOOKUP no Excel:

• Quando range_lookup for omitido, VLOOKUP permitirá correspondências não exatas, mas ainda usará correspondências exatas, se disponíveis.
• A maior limitação da função é que ela sempre assume o valor da direita. A função recuperará dados das colunas à direita da primeira coluna da tabela.
• Se a coluna de pesquisa contiver valores duplicados, a função VLOOKUP corresponderá apenas ao primeiro valor.
• A função não diferencia maiúsculas de minúsculas.
• Suponha que exista uma fórmula VLOOKUP em uma planilha. Nesse caso, as fórmulas podem quebrar se você inserir uma coluna na tabela, porque os valores do índice da coluna são codificados, que não mudam automaticamente quando as colunas são inseridas ou excluídas.
• VLOOKUP permite o uso de caracteres curinga, por exemplo, asteriscos (*) ou pontos de interrogação (?).
• Suponha que em uma tabela você esteja trabalhando com uma função que contém números inseridos como texto. Se você está apenas obtendo números como texto de uma coluna de uma tabela, isso não importa. Mas se a primeira coluna da tabela contiver números inseridos como texto, o #N/A! será exibido se o valor de pesquisa também não for texto.
• Erro #N/A! Ocorre se a função PROCV não encontrar uma correspondência para o valor_procurado fornecido.
• Erro #REF! acontece se:
  • O argumento col_index_num é maior que o número de colunas no table_array fornecido
  • A fórmula tentou fazer referência a células que não existem.
• Erro #VALOR! acontece se:
  • O argumento col_index_num é menor que 1 ou não é reconhecido como um valor numérico
  • O argumento range_lookup não é reconhecido como um dos valores lógicos TRUE ou FALSE.

Principais diferenças entre as funções VLOOKUP e XLOOKUP no Excel

S- Sim

N -Não

Ver mais:

• Como usar a função SE no Excel
• Como usar a função AVERAGEIF no Excel

Depois de saber como usar o VLookup, você também precisa conhecer os erros comuns e como corrigi-los.

Erros comuns ao usar a função VLOOKUP no Excel

O valor de pesquisa está na coluna errada

Um dos erros mais comuns que você encontrará ao usar uma fórmula VLOOKUP é que a função retorna apenas o valor #N/A. Este erro ocorre quando não é possível encontrar o valor de pesquisa que você pediu ao VLOOKUP para procurar.

Em alguns casos, você pode usar a função XLOOKUP em vez de VLOOKUP, porém, esse erro só pode ser facilmente corrigido na função VLOOKUP.

Como resolver o problema:

A razão pela qual VLOOKUP não encontrou o valor desejado pode ser porque o valor não está na tabela. No entanto, se ele retornar todos os valores #N/A, o valor de pesquisa for texto, provavelmente é porque ele só pode ler da esquerda para a direita.

Ao criar uma tabela no Excel para VLOOKUP, certifique-se de colocar o valor de pesquisa à esquerda do valor que deseja retornar. A maneira mais fácil de fazer isso é colocá-lo na primeira coluna da tabela.

Agora a fórmula retornará um valor:

No geral, VLOOKUP é uma ótima maneira de consultar dados com mais rapidez no Microsoft Excel. Embora as consultas VLOOKUP sejam executadas verticalmente em colunas e tenham algumas outras limitações, a Microsoft atualiza continuamente os recursos de pesquisa do Excel para expandir sua aplicabilidade. Por exemplo, HLOOKUP, XLOOKUP... podem ajudá-lo a procurar dados em muitas direções diferentes. LuckyTemplates.com continuará a fornecer informações sobre como usar esta função nos artigos a seguir.