Como usar o VLOOKUP no Excel

VLOOKUP é uma das funções mais úteis do Excel, e também é uma das menos compreendidas. Neste artigo, desmistificamos o VLOOKUP por meio de um exemplo da vida real. Criaremos um modelo de fatura utilizável para uma empresa fictícia.

VLOOKUP é uma função de Excel .Este artigo assumirá que o leitor já tem uma compreensão passada das funções do Excel, e pode usar funções básicas como SUM, MÉDIA e HOJE.No seu uso mais comum, o VLOOKUP é uma função de banco de dados , o que significa que ele funciona com tabelas de banco de dados - ou mais simplesmente, listas de coisas em uma planilha do Excel. Que tipo de coisas? Bem, qualquer tipo de coisa .Você pode ter uma planilha que contenha uma lista de funcionários, produtos ou clientes, ou CDs em sua coleção de CD, ou estrelas no céu noturno. Realmente não importa.

Aqui está um exemplo de uma lista ou banco de dados. Neste caso, é uma lista de produtos que nossa empresa fictícia vende:

Normalmente, listas como essa têm algum tipo de identificador exclusivo para cada item na lista. Nesse caso, o identificador exclusivo está na coluna "Código do Item".Nota: Para que a função VLOOKUP funcione com um banco de dados / lista, essa lista

deve ter uma coluna contendo o identificador exclusivo( ou "chave" ou "ID") e essa coluna deve ser a primeira coluna na tabela.Nosso banco de dados de exemplo acima satisfaz esse critério.

A parte mais difícil de usar o VLOOKUP é entender exatamente o que é para isso. Então, vejamos se podemos deixar isso claro primeiro:

VLOOKUP recupera informações de um banco de dados / lista com base em uma instância fornecida do identificador exclusivo.

No exemplo acima, você inseriria a função VLOOKUP em outra planilha com um código de item e retornaria a você a descrição do item correspondente, seu preço ou sua disponibilidade( sua quantidade "em estoque") conforme descrito em sualista original. Qual dessas informações você passará de volta? Bem, você consegue decidir isso quando você está criando a fórmula.

Se tudo o que você precisa é uma informação do banco de dados, seria muito difícil pesquisar para construir uma fórmula com uma função VLOOKUP.Normalmente, você usaria esse tipo de funcionalidade em uma planilha reutilizável, como um modelo. Cada vez que alguém introduz um código de item válido, o sistema recuperaria todas as informações necessárias sobre o item correspondente.

Vamos criar um exemplo disso: um modelo de fatura que podemos reutilizar uma e outra vez em nossa empresa fictícia.

Primeiro, iniciamos o Excel e criamos uma fatura em branco:

. É assim que vai funcionar: a pessoa que usa o modelo de fatura preencherá uma série de códigos de item na coluna "A", e o sistema recuperará cada itemdescrição e preço da base de dados do nosso produto. Essa informação será usada para calcular o total da linha para cada item( assumindo que inserimos uma quantidade válida).

Para fins de manter este exemplo simples, localizaremos o banco de dados do produto em uma folha separada no mesmo livro:

Na realidade, é mais provável que o banco de dados do produto esteja localizado em uma pasta de trabalho separada. Faz pouca diferença para a função VLOOKUP, que realmente não se importa se o banco de dados estiver localizado na mesma folha, uma folha diferente ou uma pasta de trabalho completamente diferente.

Então, criamos nosso banco de dados de produtos, que se parece com isto:

Para testar a fórmula VLOOKUP que estamos prestes a escrever, primeiro inserimos um código de item válido na célula A11 de nossa fatura em branco:

Em seguida, nósmova a célula ativa para a célula na qual desejamos que a informação obtida do banco de dados por VLOOKUP seja armazenada. Curiosamente, este é o passo que a maioria das pessoas cometeu errado. Para explicar ainda mais: Estamos prestes a criar uma fórmula VLOOKUP que irá recuperar a descrição que corresponde ao código do item na célula A11.Onde queremos que esta descrição seja colocada quando a conseguimos? Na célula B11, é claro. Então é aí que escrevemos a fórmula VLOOKUP: na célula B11.Selecione a célula B11 agora.

Precisamos localizar a lista de todas as funções disponíveis que o Excel tem para oferecer, para que possamos escolher o VLOOKUP e obter alguma ajuda para completar a fórmula. Isto é encontrado clicando primeiro na guia Fórmulas e, em seguida, clicando em Inserir Função :

Aparece uma caixa que nos permite selecionar qualquer uma das funções disponíveis no Excel.

Para encontrar o que estamos procurando, podemos digitar um termo de pesquisa como "pesquisa"( porque a função que nos interessa é uma função de pesquisa ).O sistema nos devolve uma lista de todas as funções relacionadas à pesquisa no Excel. VLOOKUP é o segundo na lista. Selecione-o e clique em OK .

Os argumentos da função aparecem na caixa , solicitando todos os argumentos ( ou parâmetros ) necessários para completar a função VLOOKUP.Você pode pensar nesta caixa como a função fazendo-nos as seguintes perguntas:

1. Que identificador exclusivo você está pesquisando no banco de dados?
2. Onde está o banco de dados?
3. Qual parte da informação do banco de dados, associada ao identificador exclusivo, você deseja ter recuperado para você?

Os três primeiros argumentos são apresentados em negrito , indicando que são argumentos obrigatórios ( a função VLOOKUP está incompleta sem eles e não retornará um valor válido).O quarto argumento não é arrojado, o que significa que é opcional:

Vamos completar os argumentos em ordem, de cima para baixo.

O primeiro argumento que precisamos completar é o argumento Lookup_value .A função precisa de nós para dizer onde encontrar o identificador exclusivo( o código do item neste caso) sobre o qual ele deve retornar a descrição. Devemos selecionar o código do item que inserimos anteriormente( no A11).

Clique no ícone do seletor à direita do primeiro argumento:

Em seguida, clique uma vez na célula que contém o código do item( A11) e pressione Digite :

O valor de "A11" é inserido no primeiro argumento.

Agora precisamos inserir um valor para o argumento Table_array .Em outras palavras, precisamos dizer ao VLOOKUP onde encontrar o banco de dados / lista. Clique no ícone do seletor ao lado do segundo argumento:

Agora localize o banco de dados / lista e selecione a lista inteira - não incluindo a linha de cabeçalho .No nosso exemplo, o banco de dados está localizado em uma planilha separada, então primeiro clicamos na guia da planilha:

Em seguida, selecionamos todo o banco de dados, não incluindo a linha de cabeçalho:

. .. e pressione Digite .O intervalo de células que representa o banco de dados( neste caso "'Product Database'! A2: D7") é inserido automaticamente para nós no segundo argumento.

Agora precisamos inserir o terceiro argumento, Col_index_num .Usamos esse argumento para especificar a VLOOKUP qual parte da informação do banco de dados, associar com nosso código de item em A11, desejamos ter retornado para nós. Neste exemplo particular, desejamos que a descrição do item seja devolvida para nós. Se você olhar na planilha do banco de dados, notará que a coluna "Descrição" é a coluna segundo no banco de dados. Isso significa que devemos inserir um valor de "2" na caixa Col_index_num :

É importante notar que não estamos entrando um "2" aqui porque a coluna "Descrição" está na coluna B emessa planilha. Se o banco de dados aconteceu para começar na coluna K da planilha, ainda inseriremos um "2" neste campo porque a coluna "Descrição" é a segunda coluna no conjunto de células que selecionamos ao especificar o "Table_array".

Finalmente, precisamos decidir se deseja inserir um valor no argumento VLOOKUP final, Range_lookup .Este argumento requer um verdadeiro ou false valor, ou deve ser deixado em branco. Ao usar o VLOOKUP com bancos de dados( como é verdade 90% do tempo), a maneira de decidir o que colocar neste argumento pode ser pensada da seguinte maneira:

Se a primeira coluna do banco de dados( a coluna que contém os identificadores exclusivos)está ordenado alfabeticamente / numericamente em ordem crescente, então é possível inserir um valor de verdadeiro neste argumento, ou deixá-lo em branco.

Se a primeira coluna do banco de dados for não ordenada, ou estiver ordenada por ordem decrescente, então deve inserir um valor de false neste argumento

Como a primeira coluna do nosso banco de dados é não ordenada,nós inserimos false neste argumento:

É isso! Nós inserimos todas as informações necessárias para o VLOOKUP retornar o valor que precisamos. Clique no botão OK e observe que a descrição correspondente ao código do item "R99245" foi inserida corretamente na célula B11:

A fórmula que foi criada para nós é a seguinte:

Se inserimos um diferente item na célulaA11, vamos começar a ver a potência da função VLOOKUP: a célula de descrição muda para coincidir com o novo código do item:

Podemos executar um conjunto de etapas semelhantes para obter o do item do item retornado para a célula E11.Observe que a nova fórmula deve ser criada na célula E11.O resultado será assim:

. .. e a fórmula será assim:

Observe que a única diferença entre as duas fórmulas é o terceiro argumento( Col_index_num ) mudou de um "2" para um "3"( porquequeremos obter dados obtidos a partir da 3ª coluna no banco de dados).

Se decidimos comprar 2 desses itens, entraríamos um "2" na célula D11.Em seguida, inserimos uma fórmula simples na célula F11 para obter o total da linha:

= D11 * E11

. .. que se parece com isso. ..

Concluindo o Modelo de Fatura

Aprendemos muito sobre o VLOOKUP até agora. Na verdade, aprendemos tudo o que vamos aprender neste artigo.É importante notar que o VLOOKUP pode ser usado em outras circunstâncias, além de bancos de dados. Isso é menos comum, e pode ser abordado em futuros artigos do How-To Geek.

Nosso modelo de fatura ainda não está concluído. Para concluí-lo, faria o seguinte:

1. Removemos o código do item de amostra da célula A11 e o "2" da célula D11.Isso fará com que nossas fórmulas VLOOKUP recém-criadas exibam mensagens de erro:
   
   Podemos remediar isso pelo uso judicioso das funções IF() e ISBLANK() do Excel. Nós mudamos a nossa fórmula a partir deste. .. = VLOOKUP( A11, 'Base de Dados do Produto'! A2: D7,2, FALSE) . .. a este. .. = IF( ISBLANK( A11), "", VLOOKUP( A11, 'Product Database'! A2: D7,2, FALSE))
   
2. Copiamos as fórmulas nas células B11, E11 e F11 até o restante das linhas do item da fatura. Note-se que, se fizermos isso, as fórmulas resultantes não serão mais referentes à tabela do banco de dados. Poderíamos corrigir isso mudando as referências de células para o banco de dados para referências de células absolutas .Alternativamente - e ainda melhor -, poderíamos criar um nome de faixa para todo o banco de dados do produto( como "Produtos") e usar esse nome de intervalo em vez das referências de células. A fórmula mudaria a partir deste. .. = IF( ISBLANK( A11), "", VLOOKUP( A11, 'Product Database'! A2: D7,2, FALSE)) . .. para este. .. = IF( ISBLANK( A11)"", VLOOKUP( A11, Produtos, 2, FALSE)) . .. e então copiar as fórmulas para o restante das linhas do item da fatura.
3. Nós provavelmente "bloquearemos" as células que contêm nossas fórmulas( ou, em vez, desbloquear as células outras ) e, em seguida, proteger a planilha, de modo a garantir que nossas fórmulas cuidadosamente construídas não sejam acidentalmente substituídas quando alguém vem preencherna nota fiscal.
4. Salvamos o arquivo como um modelo , para que ele possa ser reutilizado por todos na nossa empresa

Se nos sentindo realmente inteligente, criaríamos um banco de dados de todos os nossos clientes em outra planilha e, em seguida, usaremos oID do cliente inserida na célula F5 para preencher automaticamente o nome e o endereço do cliente nas células B6, B7 e B8.

Se você gostaria de praticar com o VLOOKUP, ou simplesmente veja o nosso Modelo de Fatura resultante, ele pode ser baixado a partir daqui.