Baixe a apostila de exemplo
Este tutorial demonstra como usar o Função VLOOKUP do Excel no Excel para pesquisar um valor.
Visão geral da função VLOOKUP
A função VLOOKUP Vlookup significa pesquisa vertical. Ele procura um valor na coluna mais à esquerda de uma tabela. Em seguida, retorna um valor um número especificado de colunas à direita do valor encontrado. É o mesmo que hlookup, exceto que procura valores verticalmente em vez de horizontalmente.
(Observe como as entradas da fórmula aparecem)
Sintaxe e argumentos da função VLOOKUP:
| 1 | = VLOOKUP (lookup_value, table_array, col_index_num, range_lookup) |
lookup_value - O valor que você deseja pesquisar.
table_array - O intervalo de dados que contém o valor que você deseja pesquisar e o valor que deseja que o vlookup retorne. A coluna que contém os valores de pesquisa deve ser a coluna mais à esquerda.
col_index_num - O número da coluna do intervalo de dados, do qual você deseja retornar um valor.
Pesquisa de alcance - Verdadeiro ou falso. FALSE procura uma correspondência exata. TRUE procura a correspondência mais próxima que seja menor ou igual a lookup_value. Se TRUE for escolhido, a coluna mais à esquerda (a coluna de pesquisa) deve ser classificada de forma ascendente (do menor para o maior).
O que é a função VLOOKUP?
Como uma das funções mais antigas no mundo das planilhas, a função VLOOKUP é usada para fazer Vético Pesquisas. Ele tem algumas limitações que geralmente são superadas com outras funções, como INDEX / MATCH. No entanto, ele tem a vantagem de ser uma função única que pode fazer uma pesquisa de correspondência exata por si só. Também tende a ser uma das primeiras funções que as pessoas aprendem.
Exemplo básico
Vejamos uma amostra de dados de um livro de notas. Abordaremos vários exemplos de extração de informações para alunos específicos.
Se quisermos descobrir em que classe Bob está, escreveríamos a fórmula:
| 1 | = PROCV ("Bob", A2: C5, 2, FALSO) |
Coisas importantes a lembrar são que o item que estamos procurando (Bob) deve estar na primeira coluna do nosso intervalo de pesquisa (A2: C5). Dissemos à função que queremos retornar um valor de 2WL coluna, que neste caso é a coluna B. Finalmente, indicamos que queremos fazer um Combinação exata colocando False como o último argumento. Aqui, a resposta será “Ler”.
Dica lateral: Você também pode usar o número 0 em vez de False como o argumento final, pois eles têm o mesmo valor. Algumas pessoas preferem isso porque é mais rápido de escrever. Apenas saiba que ambos são aceitáveis.
Dados Deslocados
Para adicionar algum esclarecimento ao nosso primeiro exemplo, o item de pesquisa não precisa estar na coluna A da sua planilha, apenas na primeira coluna do seu intervalo de pesquisa. Vamos usar o mesmo conjunto de dados:
Agora, vamos encontrar a nota da aula de Ciências. Nossa fórmula seria
| 1 | = PROCV ("Ciência", B2: C5, 2, FALSO) |
Esta ainda é uma fórmula válida, pois a primeira coluna do nosso intervalo de pesquisa é a coluna B, que é onde nosso termo de pesquisa “Ciência” será encontrado. Estamos retornando um valor de 2WL coluna do intervalo de pesquisa, que neste caso é a coluna C. A resposta então é “A-“.
Uso de curinga
A função VLOOKUP suporta o uso dos curingas “*” e “?” ao fazer pesquisas. Por exemplo, digamos que esquecemos como soletrar o nome de Frank e só queríamos pesquisar um nome que comece com “F”. Poderíamos escrever a fórmula
| 1 | = PROCV ("F *", A2: C5, 2, FALSO) |
Isso seria capaz de encontrar o nome Frank na linha 5 e, em seguida, retornar o valor de 2WL coluna relativa. Nesse caso, a resposta será “Ciência”.
Correspondência não exata
Na maioria das vezes, você desejará ter certeza de que o último argumento em PROCV é False (ou 0) para obter uma correspondência exata. No entanto, algumas vezes você pode estar procurando uma correspondência não exata. Se você tiver uma lista de dados classificados, também pode usar VLOOKUP para retornar o resultado para o item que é o mesmo ou o menor seguinte. Isso geralmente é usado ao lidar com intervalos crescentes de números, como em uma tabela de impostos ou bônus de comissão.
Digamos que você deseja encontrar a taxa de imposto para uma renda inserida na célula D2. A fórmula em D4 pode ser:
| 1 | = PROCV (D2, A2: B6, 2, VERDADEIRO) |
A diferença nesta fórmula é que nosso último argumento é “Verdadeiro”. Em nosso exemplo específico, podemos ver que quando nosso indivíduo insere uma renda de $ 45.000, eles terão uma alíquota de imposto de 15%.
Observação: Embora geralmente estejamos querendo uma correspondência exata com False como o argumento, se você esquecer de especificar o 4º em uma PROCV, o padrão é Verdadeiro. Isso pode fazer com que você obtenha alguns resultados inesperados, especialmente ao lidar com valores de texto.
Coluna dinâmica
PROCV requer que você forneça um argumento dizendo de qual coluna você deseja retornar um valor, mas pode surgir uma ocasião em que você não sabe onde a coluna estará, ou você deseja permitir que seu usuário altere de qual coluna retornar. Nestes casos, pode ser útil usar a função MATCH para determinar o número da coluna.
Vamos considerar nosso exemplo de livro de notas novamente, com algumas entradas em E2 e E4. Para obter o número da coluna, poderíamos escrever uma fórmula de
| 1 | = CORRESPONDÊNCIA (E2, A1: C1, 0) |
Isso tentará encontrar a posição exata de “Grade” dentro do intervalo A1: C1. A resposta será 3. Sabendo disso, podemos conectá-lo a uma função VLOOKUP e escrever uma fórmula em E6 assim:
| 1 | = PROCV (E4, A2: C5, CORRESPONDÊNCIA (E2, A1: C1, 0), 0) |
Então, a função MATCH será avaliada como 3, e isso diz a VLOOKUP para retornar um resultado de 3rd coluna no intervalo A2: C5. No geral, obtemos nosso resultado desejado de “C”. Nossa fórmula é dinâmica agora, pois podemos alterar a coluna a ser examinada ou o nome a ser pesquisado.
Limitações de VLOOKUP
Conforme mencionado no início do artigo, a maior desvantagem de VLOOKUP é que ele requer que o termo de pesquisa seja encontrado na coluna mais à esquerda do intervalo de pesquisa. Embora existam alguns truques sofisticados que você pode fazer para superar isso, a alternativa comum é usar INDEX e MATCH. Essa combinação oferece mais flexibilidade e, às vezes, pode até ser um cálculo mais rápido.
VLOOKUP no Google Sheets
A função VLOOKUP funciona exatamente da mesma forma no Planilhas Google e no Excel:
Notas Adicionais
Use a função PROCV para realizar uma pesquisa VERTICAL. O VLOOKUP procura uma correspondência exata (Pesquisa de alcance = FALSE) ou a correspondência mais próxima que é igual ou menor que lookup_value (Pesquisa de alcance = TRUE, apenas valores numéricos) na primeira linha de table_array. Em seguida, ele retorna um valor correspondente, n número de linhas abaixo da correspondência.
Ao usar um PROCV para encontrar uma correspondência exata, primeiro você define um valor de identificação que deseja pesquisar como o lookup_value. Esse valor de identificação pode ser um SSN, ID de funcionário, nome ou algum outro identificador exclusivo.
Em seguida, você define o intervalo (chamado de table_array) que contém os identificadores na linha superior e quaisquer valores que você deseja pesquisar nas linhas abaixo. IMPORTANTE: Os identificadores exclusivos devem estar na linha superior. Se não estiverem, você deve mover a linha para o topo ou usar MATCH / INDEX em vez de VLOOKUP.
Terceiro, defina o número da linha (row_index) do table_array que você deseja retornar. Lembre-se de que a primeira linha, contendo os identificadores exclusivos, é a linha 1. A segunda linha é a linha 2 etc.
Por último, você deve indicar se deseja pesquisar uma correspondência exata (FALSO) ou correspondência mais próxima (VERDADEIRO) no Pesquisa de alcance. Se a opção de correspondência exata for selecionada e uma correspondência exata não for encontrada, um erro será retornado (# N / A). Para que a fórmula retorne em branco ou “não encontrado”, ou qualquer outro valor ao invés do valor de erro (# N / A), use a função IFERROR com VLOOKUP.
Para usar a função PROCV para retornar um conjunto de correspondência aproximada: Pesquisa de alcance = VERDADEIRO. Esta opção está disponível apenas para valores numéricos. Os valores devem ser classificados em ordem crescente.
Para realizar uma pesquisa horizontal, use a função HLOOKUP.
Exemplos VLOOKUP em VBA
Você também pode usar a função VLOOKUP no VBA. Modelo:application.worksheetfunction.vlookup (lookup_value, table_array, col_index_num, range_lookup)
Para os argumentos da função (lookup_value, etc.), você pode inseri-los diretamente na função ou definir as variáveis a serem usadas.
Voltar para a lista de todas as funções no Excel
