10 exemplos de VLOOKUP para usuários iniciantes e avançados

Função VLOOKUP - Introdução

A função VLOOKUP é o benchmark.

Você sabe alguma coisa no Excel se souber usar a função PROCV.

Do contrário, é melhor não listar o Excel como uma de suas áreas fortes em seu currículo.

Participei de painéis de entrevistas em que assim que o candidato mencionou o Excel como sua área de especialização, a primeira coisa que perguntou foi - você entendeu - a função VLOOKUP.

Agora que sabemos o quão importante é esta função do Excel, faz sentido acertá-la completamente para poder dizer com orgulho - “Eu sei uma ou duas coisas no Excel”.

Este será um grande tutorial de VLOOKUP (pelos meus padrões).

Vou cobrir tudo o que há para saber sobre isso e, em seguida, mostrar exemplos úteis e práticos de VLOOKUP.

Então aperte o cinto.

É hora da decolagem.

Quando usar a função PROCV no Excel?

A função VLOOKUP é mais adequada para situações em que você está procurando um ponto de dados correspondente em uma coluna e, quando o ponto de dados correspondente é encontrado, você vai para a direita nessa linha e busca um valor de uma célula que é um número especificado de colunas à direita.

Vamos dar um exemplo simples aqui para entender quando usar o Vlookup no Excel.

Lembro quando a lista de pontuação do exame estava fora e colada no quadro de avisos e todo mundo ficava louco para encontrar seus nomes e sua pontuação (pelo menos é o que costumava acontecer quando eu estava na escola).

Funcionou assim:

• Você vai até o quadro de avisos e começa a procurar seu nome ou número de inscrição (passando o dedo de cima para baixo na lista).
• Assim que identificar seu nome, você move seus olhos para a direita do nome / número de inscrição para ver sua pontuação.

E é exatamente isso que a função VLOOKUP do Excel faz por você (sinta-se à vontade para usar este exemplo em sua próxima entrevista).

A função VLOOKUP procura por um valor especificado em uma coluna (no exemplo acima, era o seu nome) e quando ela encontra a correspondência especificada, ela retorna um valor na mesma linha (as marcas que você obteve).

Sintaxe

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

Argumentos de entrada

• lookup_value - este é o valor de pesquisa que você está tentando encontrar na coluna mais à esquerda de uma tabela. Pode ser um valor, uma referência de célula ou uma string de texto. No exemplo da folha de pontuação, este seria o seu nome.
• table_array - esta é a matriz da tabela na qual você está procurando o valor. Isso pode ser uma referência a um intervalo de células ou um intervalo nomeado. No exemplo da folha de pontuação, esta seria a tabela inteira que contém a pontuação de todos para cada disciplina
• col_index - este é o número do índice da coluna da qual você deseja buscar o valor correspondente. No exemplo da folha de pontuação, se você quiser as pontuações de Matemática (que é a primeira coluna de uma tabela que contém as pontuações), você deve procurar na coluna 1. Se quiser as pontuações de Física, deve procurar na coluna 2
• [Pesquisa de alcance] - aqui você especifica se deseja uma correspondência exata ou aproximada. Se omitido, o padrão é VERDADEIRO - correspondência aproximada (veja as notas adicionais abaixo).

Notas adicionais (chato, mas importante saber)

• A correspondência pode ser exata (FALSE ou 0 em range_lookup) ou aproximada (TRUE ou 1).
• Na pesquisa aproximada, certifique-se de que a lista esteja classificada em ordem crescente (de cima para baixo), caso contrário, o resultado pode ser impreciso.
• Quando range_lookup é TRUE (pesquisa aproximada) e os dados são classificados em ordem crescente:
  • Se a função VLOOKUP não puder encontrar o valor, ela retornará o maior valor, que é menor que lookup_value.
  • Ele retornará um erro # N / A se lookup_value for menor que o menor valor.
  • Se lookup_value for texto, caracteres curinga podem ser usados ​​(consulte o exemplo abaixo).

Agora, esperando que você tenha um entendimento básico do que a função VLOOKUP pode fazer, vamos descascar esta cebola e ver alguns exemplos práticos da função VLOOKUP.

10 exemplos de VLOOKUP do Excel (básico e avançado)

Aqui estão 10 exemplos úteis de uso do Excel Vlookup que mostrarão como usá-lo em seu trabalho diário.

Exemplo 1 - Encontrando a pontuação de matemática de Brad

No exemplo VLOOKUP abaixo, eu tenho uma lista com nomes de alunos na coluna mais à esquerda e marcas em diferentes disciplinas nas colunas B a E.

Agora vamos trabalhar e usar a função PROCV para o que ela faz de melhor. A partir dos dados acima, preciso saber quanto Brad marcou em matemática.

A partir dos dados acima, preciso saber quanto Brad marcou em matemática.

Aqui está a fórmula VLOOKUP que retornará a pontuação de matemática de Brad:

= PROCV ("Brad", $ A $ 3: $ E $ 10,2,0)

A fórmula acima tem quatro argumentos:

• “Brad: - este é o valor de pesquisa.
• $ A $ 3: $ E $ 10 - este é o intervalo de células em que estamos olhando. Lembre-se de que o Excel procura o valor de pesquisa na coluna mais à esquerda. Neste exemplo, ele procuraria o nome Brad em A3: A10 (que é a coluna mais à esquerda da matriz especificada).
• 2 - Assim que a função detectar o nome de Brad, ela irá para a segunda coluna da matriz e retornará o valor na mesma linha de Brad. O valor 2 aqui indica que estamos procurando a pontuação da segunda coluna da matriz especificada.
• 0 - diz à função PROCV para procurar apenas correspondências exatas.

Aqui está como a fórmula VLOOKUP funciona no exemplo acima.

Primeiro, ele procura o valor Brad na coluna mais à esquerda. Vai de cima para baixo e encontra o valor na célula A6.

Assim que encontra o valor, ele vai para a direita na segunda coluna e busca o valor nela.

Você pode usar a mesma construção de fórmula para obter as notas de qualquer pessoa em qualquer uma das disciplinas.

Por exemplo, para encontrar as notas de Maria em Química, use a seguinte fórmula VLOOKUP:

= PROCV ("Maria", $ A $ 3: $ E $ 10,4,0)

No exemplo acima, o valor de pesquisa (nome do aluno) é inserido entre aspas duplas. Você também pode usar uma referência de célula que contém o valor de pesquisa.

A vantagem de usar uma referência de célula é que ela torna a fórmula dinâmica.

Por exemplo, se você tiver uma célula com o nome de um aluno e estiver obtendo a pontuação de matemática, o resultado será atualizado automaticamente quando você alterar o nome do aluno (conforme mostrado abaixo):

Se você inserir um valor de pesquisa que não foi encontrado na coluna mais à esquerda, ele retornará um erro # N / A.

Exemplo 2 - Pesquisa bidirecional

No Exemplo 1 acima, codificamos o valor da coluna. Portanto, a fórmula sempre retornaria a pontuação do Math, pois usamos 2 como o número do índice da coluna.

Mas e se você quiser tornar o valor VLOOKUP e o número do índice da coluna dinâmicos. Por exemplo, conforme mostrado abaixo, você pode alterar o nome do aluno ou o nome da disciplina, e a fórmula VLOOKUP obtém a pontuação correta. Este é um exemplo de uma fórmula VLOOKUP bidirecional.

Este é um exemplo de uma função VLOOKUP bidirecional.

Para fazer essa fórmula de pesquisa bidirecional, você também precisa tornar a coluna dinâmica. Assim, quando um usuário muda de assunto, a fórmula escolhe automaticamente a coluna correta (2 no caso de Matemática, 3 no caso de Física, etc.).

Para fazer isso, você precisa usar a função MATCH como o argumento da coluna.

Aqui está a fórmula VLOOKUP que fará isso:

= PROCURA (G4, $ A $ 3: $ E $ 10, CORRESPONDÊNCIA (H3, $ A $ 2: $ E $ 2,0), 0)

A fórmula acima usa MATCH (H3, $ A $ 2: $ E $ 2,0) como o número da coluna. A função MATCH recebe o nome do assunto como o valor de pesquisa (em H3) e retorna sua posição em A2: E2. Portanto, se você usar Math, ele retornará 2, já que Math é encontrado em B2 (que é a segunda célula no intervalo de array especificado).

Exemplo 3 - Usando listas suspensas como valores de pesquisa

No exemplo acima, temos que inserir os dados manualmente. Isso pode ser demorado e sujeito a erros, especialmente se você tiver uma lista enorme de valores de pesquisa.

Uma boa ideia nesses casos é criar uma lista suspensa dos valores de pesquisa (neste caso, podem ser nomes de alunos e disciplinas) e, em seguida, simplesmente escolher na lista.

Com base na seleção, a fórmula atualizaria automaticamente o resultado.

Algo conforme mostrado abaixo:

Isso é um bom componente do painel, pois você pode ter um grande conjunto de dados com centenas de alunos no back end, mas o usuário final (digamos um professor) pode obter rapidamente as notas de um aluno em uma disciplina simplesmente fazendo as seleções de a lista suspensa.

Como fazer isso:

A fórmula VLOOKUP usada neste caso é a mesma usada no Exemplo 2.

= PROCURA (G4, $ A $ 3: $ E $ 10, CORRESPONDÊNCIA (H3, $ A $ 2: $ E $ 2,0), 0)

Os valores de pesquisa foram convertidos em listas suspensas.

Aqui estão as etapas para criar a lista suspensa:

• Selecione a célula na qual deseja a lista suspensa. Neste exemplo, em G4, queremos os nomes dos alunos.
• Vá para Dados -> Ferramentas de dados -> Validação de dados.
• Na caixa Diálogo de validação de dados, na guia de configurações, selecione Lista no menu suspenso Permitir.
• Na fonte, selecione $ A $ 3: $ A $ 10
• Clique OK.

Agora você terá a lista suspensa na célula G4. Da mesma forma, você pode criar um em H3 para os assuntos.

Exemplo 4 - Pesquisa de três vias

O que é uma pesquisa de três vias?

No Exemplo 2, usamos uma tabela de pesquisa com pontuações para alunos em diferentes disciplinas. Este é um exemplo de uma pesquisa bidirecional, pois usamos duas variáveis ​​para buscar a pontuação (o nome do aluno e o nome do sujeito).

Agora, suponha que em um ano, um aluno tenha três níveis diferentes de exames, teste de unidade, meio-termo e exame final (era o que eu tinha quando era aluno).

Uma pesquisa de três vias seria a capacidade de obter as notas de um aluno para um determinado assunto do nível de exame especificado.

Algo conforme mostrado abaixo:

No exemplo acima, a função VLOOKUP pode pesquisar em três tabelas diferentes (Teste de Unidade, Meio do Termo e Exame Final) e retorna a pontuação do aluno especificado na disciplina especificada.

Aqui está a fórmula usada na célula H4:

= PROCV (G4, ESCOLHER (SE (H2 = "Teste de unidade", 1, SE (H2 = "Intervalo", 2,3)), $ A $ 3: $ E $ 7, $ A $ 11: $ E $ 15, $ A $ 19: $ E $ 23), CORRESPONDÊNCIA (H3, $ A $ 2: $ E $ 2,0), 0) 

Esta fórmula usa a função CHOOSE para garantir que a tabela correta seja consultada. Vamos analisar a parte ESCOLHER da fórmula:

ESCOLHER (SE (H2 = ”Teste de unidade”, 1, SE (H2 = ”Intervalo”, 2,3)), $ A $ 3: $ E $ 7, $ A $ 11: $ E $ 15, $ A $ 19: $ E $ 23 )

O primeiro argumento da fórmula é SE (H2 = ”Teste de Unidade”, 1, SE (H2 = ”Meio do período”, 2,3)), que verifica a célula H2 e vê a que nível de exame está sendo referido. Se for um Teste de Unidade, ele retorna $ A $ 3: $ E $ 7, que tem as pontuações do Teste de Unidade. Se for a meio do prazo, retorna $ A $ 11: $ E $ 15, caso contrário, retorna $ A $ 19: $ E $ 23.

Isso torna a matriz da tabela VLOOKUP dinâmica e, portanto, uma pesquisa de três vias.

Exemplo 5 - Obtendo o último valor de uma lista

Você pode criar uma fórmula VLOOKUP para obter o último valor numérico de uma lista.

O maior número positivo que você pode usar no Excel é 9,999999999999999E + 307. Isso também significa que o maior número de pesquisa no número VLOOKUP também é o mesmo.

Eu não acho que você precisaria de qualquer cálculo envolvendo um número tão grande. E é exatamente isso que podemos usar para obter o último número de uma lista.

Suponha que você tenha um conjunto de dados (em A1: A14) como mostrado abaixo e você deseja obter o último número da lista.

Aqui está a fórmula que você pode usar:

= VLOOKUP (9,999999999999999E + 307, $ A $ 1: $ A $ 14,VERDADEIRO)

Observe que a fórmula acima usa uma correspondência aproximada VLOOKUP (observe TRUE no final da fórmula, em vez de FALSE ou 0). Além disso, observe que a lista não precisa ser classificada para que esta fórmula VLOOKUP funcione.

Aqui está como funciona a função VLOOKUP aproximada. Ele verifica a coluna mais à esquerda de cima para baixo.

• Se encontrar uma correspondência exata, ele retornará esse valor.
• Se encontrar um valor superior ao valor de pesquisa, ele retornará o valor na célula acima dele.
• Se o valor de pesquisa for maior do que todos os valores da lista, ele retornará o último valor.

No exemplo acima, o terceiro cenário está em ação.

Desde 9,999999999999999E + 307 é o maior número que pode ser usado no Excel, quando usado como valor de pesquisa retorna o último número da lista.

Da mesma forma, você também pode usá-lo para retornar o último item de texto da lista. Aqui está a fórmula que pode fazer isso:

= PROCV ("zzz", $ A $ 1: $ A $ 8,1,VERDADEIRO)

A mesma lógica segue. O Excel examina todos os nomes e, como zzz é considerado maior do que qualquer nome / texto começando com alfabetos antes de zzz, ele retornaria o último item da lista.

Exemplo 6 - Pesquisa parcial usando caracteres curinga e VLOOKUP

Os caracteres curinga do Excel podem ser realmente úteis em muitas situações.

É aquela poção mágica que dá superpoderes às suas fórmulas.

A consulta parcial é necessária quando você precisa procurar um valor em uma lista e não há uma correspondência exata.

Por exemplo, suponha que você tenha um conjunto de dados conforme mostrado abaixo e deseja procurar a empresa ABC em uma lista, mas a lista possui ABC Ltd em vez de ABC.

Você não pode usar ABC como o valor de pesquisa, pois não há correspondência exata na coluna A. A correspondência aproximada também leva a resultados errôneos e exige que a lista seja classificada em ordem crescente.

No entanto, você pode usar um caractere curinga na função PROCV para obter a correspondência.

Insira a seguinte fórmula na célula D2 e ​​arraste-a para as outras células:

= PROCURA ("*" & C2 & "*", $ A $ 2: $ A $ 8,1, FALSO)

Como essa fórmula funciona?

Na fórmula acima, em vez de usar o valor de pesquisa como está, ele é flanqueado em ambos os lados com o caractere curinga asterisco (*) - “*” & C2 & ”*”

Um asterisco é um caractere curinga no Excel e pode representar qualquer número de caracteres.

Usar o asterisco em ambos os lados do valor de pesquisa informa ao Excel que ele precisa procurar qualquer texto que contenha a palavra em C2. Ele pode ter qualquer número de caracteres antes ou depois do texto em C2.

Por exemplo, a célula C2 tem ABC, então a função VLOOKUP examina os nomes em A2: A8 e procura ABC. Ele encontra uma correspondência na célula A2, pois contém ABC em ABC Ltd. Não importa se há caracteres à esquerda ou à direita de ABC. Até que haja ABC em uma string de texto, ela será considerada uma correspondência.

Observação: a função VLOOKUP sempre retorna o primeiro valor correspondente e para de procurar mais. Então, se você tem ABC Ltd., e ABC Corporation em uma lista, ele retornará o primeiro e ignorará o resto.

Exemplo 7 - VLOOKUP retornando um erro apesar de uma correspondência no valor de pesquisa

Você pode ficar louco ao ver que há um valor de pesquisa correspondente e a função VLOOKUP está retornando um erro.

Por exemplo, no caso abaixo, há uma correspondência (Matt), mas a função VLOOKUP ainda retorna um erro.

Agora, embora possamos ver que há uma correspondência, o que não podemos ver a olho nu é que pode haver espaços à esquerda ou à direita. Se você tiver esses espaços adicionais antes, depois ou entre os valores de pesquisa, NÃO É uma correspondência exata.

Geralmente, esse é o caso quando você importa dados de um banco de dados ou os obtém de outra pessoa. Esses espaços à esquerda / à direita têm uma tendência a entrar furtivamente.

A solução aqui é a função TRIM. Ele remove todos os espaços iniciais ou finais ou espaços extras entre as palavras.

Aqui está a fórmula que lhe dará o resultado certo.

= PROCV ("Matt", TRIM ($ A $ 2: $ A $ 9), 1,0)

Como esta é uma fórmula de matriz, use Control + Shift + Enter em vez de apenas Enter.

Outra maneira poderia ser primeiro tratar sua matriz de pesquisa com a função TRIM para certificar-se de que todos os espaços adicionais foram eliminados e, em seguida, usar a função PROCV como de costume.

Exemplo 8 - Fazendo uma pesquisa que diferencia maiúsculas de minúsculas

Por padrão, o valor de pesquisa na função VLOOKUP não diferencia maiúsculas de minúsculas. Por exemplo, se seu valor de pesquisa for MATT, matt ou Matt, é tudo o mesmo para a função VLOOKUP. Ele retornará o primeiro valor correspondente, independentemente do caso.

Mas se você quiser fazer uma pesquisa com distinção entre maiúsculas e minúsculas, precisará usar a função EXACT junto com a função VLOOKUP.

Aqui está um exemplo:

Como você pode ver, existem três células com o mesmo nome (em A2, A4 e A5), mas com letras maiúsculas e minúsculas diferentes. À direita, temos os três nomes (Matt, MATT e matt) junto com suas pontuações em matemática.

Agora, a função VLOOKUP não está equipada para lidar com valores de pesquisa com distinção entre maiúsculas e minúsculas. Neste exemplo acima, ele sempre retornaria 38, que é a pontuação de Matt em A2.

Para fazer distinção entre maiúsculas e minúsculas, precisamos usar uma coluna auxiliar (conforme mostrado abaixo):

Para obter os valores na coluna auxiliar, use a função = ROW (). Ele simplesmente obterá o número da linha na célula.

Depois de ter a coluna auxiliar, aqui está a fórmula que fornecerá o resultado da pesquisa que diferencia maiúsculas de minúsculas.

= VLOOKUP (MAX (EXACT (E2, $ A $ 2: $ A $ 9) * (ROW ($ A $ 2: $ A $ 9))), $ B $ 2: $ C $ 9,2,0)

Agora vamos analisar e entender o que isso faz:

• EXACT (E2, $ A $ 2: $ A $ 9) - Esta parte compararia o valor de pesquisa em E2 com todos os valores em A2: A9. Ele retorna uma matriz de TRUEs / FALSEs onde TRUE é retornado onde há uma correspondência exata. Nesse caso, ele retornaria a seguinte matriz: {TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}.
• EXACT (E2, $ A $ 2: $ A $ 9) * (ROW ($ A $ 2: $ A $ 9) - Esta parte multiplica a matriz de VERDADEIROS / FALSOS pelo número da linha. Sempre que houver um VERDADEIRO, ele fornece o número da linha , caso contrário, dá 0. Nesse caso, ele retornaria {2; 0; 0; 0; 0; 0; 0; 0}.
• MAX (EXACT (E2, $ A $ 2: $ A $ 9) * (ROW ($ A $ 2: $ A $ 9))) - Esta parte retorna o valor máximo da matriz de números. Nesse caso, ele retornaria 2 (que é o número da linha onde há uma correspondência exata).
• Agora, simplesmente usamos este número como o valor de pesquisa e usamos a matriz de pesquisa como B2: C9

Observação: como esta é uma fórmula de matriz, use Control + Shift + Enter em vez de apenas inserir.

Exemplo 9 - Usando VLOOKUP com vários critérios

A função VLOOKUP do Excel, em sua forma básica, pode procurar um valor de pesquisa e retornar o valor correspondente da linha especificada.

Mas frequentemente é necessário usar PROCV no Excel com vários critérios.

Suponha que você tenha dados com o nome do aluno, o tipo de exame e a pontuação de matemática (conforme mostrado abaixo):

Usar a função PROCV para obter a pontuação de matemática de cada aluno para os respectivos níveis de exame pode ser um desafio.

Por exemplo, se você tentar usar VLOOKUP com Matt como o valor de pesquisa, ele sempre retornará 91, que é a pontuação da primeira ocorrência de Matt na lista. Para obter a pontuação de Matt para cada tipo de exame (teste de unidade, meio-termo e final), você precisa criar um valor de pesquisa exclusivo.

Isso pode ser feito usando a coluna auxiliar. A primeira etapa é inserir uma coluna auxiliar à esquerda das pontuações.

Agora, para criar um qualificador exclusivo para cada instância do nome, use a seguinte fórmula em C2: = A2 & ”|” & B2

Copie esta fórmula para todas as células na coluna auxiliar. Isso criará valores de pesquisa exclusivos para cada instância de um nome (conforme mostrado abaixo):

Agora, enquanto houve repetição dos nomes, não há repetição quando o nome é combinado com o nível de exame.

Isso torna mais fácil, pois agora você pode usar os valores da coluna auxiliar como os valores de pesquisa.

Aqui está a fórmula que dará o resultado em G3: I8.

= PROCV ($ F3 & "|" & G $ 2, $ C $ 2: $ D $ 19,2,0)

Aqui, combinamos o nome do aluno e o nível de exame para obter o valor de pesquisa e usamos esse valor de pesquisa e o verificamos na coluna auxiliar para obter o registro correspondente.

Nota: No exemplo acima, usamos | como separador ao juntar o texto na coluna auxiliar. Em algumas condições excepcionalmente raras (mas possíveis), você pode ter dois critérios que são diferentes, mas acabam dando o mesmo resultado quando combinados. Aqui está um exemplo:

Observe que enquanto A2 e A3 são diferentes e B2 e B3 são diferentes, as combinações acabam sendo as mesmas. Mas se você usar um separador, mesmo a combinação seria diferente (D2 e D3).

Aqui está um tutorial sobre como usar VLOOKUP com vários critérios sem usar colunas auxiliares. Você também pode assistir meu vídeo tutorial aqui.

Exemplo 10 - Tratamento de erros ao usar a função VLOOKUP

A função VLOOKUP do Excel retorna um erro quando não consegue encontrar o valor de pesquisa especificado. Você pode não querer que o valor de erro feio atrapalhe a estética de seus dados, caso VLOOKUP não consiga encontrar um valor.

Você pode remover facilmente os valores de erro com qualquer significado de texto completo, como “Não disponível” ou “Não encontrado”.

Por exemplo, no exemplo abaixo, quando você tenta encontrar a pontuação de Brad na lista, ele retorna um erro porque o nome de Brad não está na lista.

Para remover esse erro e substituí-lo por algo significativo, envolva sua função VLOOKUP na função IFERROR.

Aqui está a fórmula:

= IFERROR (PROCV (D2, $ A $ 2: $ B $ 7,2,0), "Não encontrado")

A função IFERROR verifica se o valor retornado pelo primeiro argumento (que é a função VLOOKUP neste caso) é um erro ou não. Se não for um erro, ele retorna o valor pela função VLOOKUP, caso contrário, retorna Não encontrado.

A função IFERROR está disponível a partir do Excel 2007. Se você estiver usando versões anteriores a essa, use a seguinte função:

= IF (ISERROR (VLOOKUP (D2, $ A $ 2: $ B $ 7,2,0)), "Não encontrado", VLOOKUP (D2, $ A $ 2: $ B $ 7,2,0))

Veja também: Como lidar com erros de PROCV no Excel.

É isso neste tutorial de VLOOKUP.

Tentei cobrir os principais exemplos de uso da função Vlookup no Excel. Se você gostaria de ver mais exemplos adicionados a esta lista, deixe-me saber na seção de comentários.

Nota: Tentei o meu melhor para revisar este tutorial, mas caso você encontre algum erro ortográfico, por favor me avise 🙂

Usando Função VLOOKUP no Excel - Vídeo

• Função HLOOKUP do Excel.
• Função Excel XLOOKUP
• Função INDEX do Excel.
• Função INDIRETA do Excel.
• Função Excel MATCH.
• Função Excel OFFSET.