Como usar uma fórmula VLOOKUP para obter dados de uma lista do Excel. Solucione problemas de VLOOKUP quando as coisas derem errado. Vídeos passo a passo e pastas de trabalho do Excel com exemplos VLOOKUP.
Vídeo VLOOKUP
Assista às etapas para criar uma fórmula VLOOKUP no vídeo de pesquisa Preço do produto, mostrado abaixo. As instruções escritas estão abaixo do vídeo. Obtenha o arquivo de amostra de Consulta de Preço do Produto para acompanhar o vídeo (baixe o arquivo # 4).
Selecione um local para uma Tabela de Consulta
É uma boa ideia armazenar cada consulta tabela em uma planilha separada na pasta de trabalho. Então, ao adicionar e excluir linhas nas tabelas de pesquisa, você não adicionará ou excluirá acidentalmente linhas em qualquer outra tabela.
Neste exemplo, a tabela de pesquisa está em uma planilha chamada Produtos.
Crie uma tabela de pesquisa
As fórmulas de pesquisa podem funcionar verticalmente, procurando valores abaixo de uma coluna, ou podem funcionar horizontalmente, procurando valores em uma linha . Neste exemplo, as informações serão armazenadas verticalmente, com os valores em uma coluna, e mais tarde usaremos uma fórmula VLookup para fazer uma pesquisa vertical.
- Insira os cabeçalhos na primeira linha
- A primeira coluna deve conter os valores-chave únicos nos quais você baseará a pesquisa. Neste exemplo, você pode encontrar o preço para um código de produto específico.
- Se você tiver outros dados na planilha, deixe pelo menos uma linha em branco na parte inferior da tabela e uma coluna em branco à direita da tabela, para separar o tabela de consulta a partir de outros dados.
Observação: para facilitar a referência à tabela, você pode nomear o intervalo. Há instruções aqui: Nomeando um intervalo ▲ TOPO
Argumentos da função VLOOKUP
A função VLOOKUP tem quatro argumentos:
- lookup_value : Qual valor você deseja pesquisar? Neste exemplo, o código do produto está na célula A7 e você deseja encontrar seu pr nome do oduct.
- table_array: onde está a tabela de pesquisa? Se você usar uma referência absoluta ($ A $ 2: $ C $ 5), em vez de uma referência relativa (A2: C5), será mais fácil copiar a fórmula para outras células. Ou nomeie a tabela de pesquisa e refira-se a ela pelo nome.
- col_index_num: Qual coluna tem o valor que você deseja retornar? Neste exemplo, os nomes dos produtos estão na segunda coluna da tabela de pesquisa.
- : Você deseja uma correspondência exata? Uma correspondência aproximada está correta?
Se você usar TRUE como o último argumento ou omitir o último argumento, uma correspondência aproximada pode ser retornada. Este exemplo tem FALSE como último argumento, portanto, se o código do produto não for encontrado, o resultado será # N / A. (Observação: o Excel é bastante tolerante e aceitará 0 em vez de FALSO e 1 em vez de VERDADEIRO.) ▲ TOPO
Crie uma fórmula de VLookup
Depois de criar a tabela de pesquisa, você pode criar outras fórmulas na pasta de trabalho e obter informações da lista de produtos. Por exemplo, para preparar uma fatura, você pode inserir um código de produto e as fórmulas obterão o nome do produto ou preço da tabela de produtos.
Neste exemplo, a fatura é criada em uma folha chamada Fatura. A fórmula VLOOKUP deve encontrar uma correspondência exata para o código do produto e retornar o nome do produto.
Para criar a fórmula VLOOKUP que calcula o preço do produto, siga estas etapas:
- Selecione a folha de fatura
- Insira o código do produto A23 na célula A7
- Na célula B7, inicie a fórmula VLOOKUP:
= VLOOKUP ( - Clique em célula A7 para inserir sua referência.
- Digite uma vírgula para encerrar o primeiro argumento
- Selecione a planilha Produtos
- Selecione as células A2: C5 (a lista de produtos )
- Pressione a tecla F4 para alterar as referências de célula para absolutas. A fórmula agora deve ser semelhante a:
= VLOOKUP (A7, Produtos! $ A $ 2: $ C $ 5 - Digite uma vírgula para encerrar o segundo argumento.
- Digite 2, que é a coluna na tabela de pesquisa que contém o nome do produto.
- Digite uma vírgula para encerrar o terceiro argumento.
- Digite FALSE para especificar que uma correspondência exata para o código do produto foi encontrada e adicione o colchete de fechamento.
- A fórmula agora deve ser l como isto:
= PROCV (A7, Produtos! $ A $ 2: $ C $ 5,2, FALSO) - Pressione a tecla Enter para completar a fórmula. O nome do produto será exibido.
Nota: Para retornar o preço do produto, crie uma fórmula VLOOKUP que se refira à coluna 3 da tabela de pesquisa. Por exemplo, insira a seguinte fórmula na célula C7:
= VLOOKUP (A7, Produtos! $ A $ 2: $ C $ 5,3, FALSO) ▲ TOPO
Fórmula VLOOKUP para intervalo de valores
Em algumas situações, uma correspondência aproximada é preferida, portanto, vários valores retornarão o mesmo resultado. Por exemplo, ao avaliar os trabalhos dos alunos, todos os trabalhos com uma nota de 85 ou mais devem receber uma nota A.
Para ver as etapas para criar esta fórmula, assista ao vídeo VLOOKUP mostrado abaixo. As instruções escritas estão abaixo do vídeo.
Neste exemplo, a tabela de pesquisa é criada em uma planilha chamada Notas. Para criar a tabela de pesquisa, insira a pontuação mínima para cada nota na coluna A. Insira a nota correspondente na coluna B. Classifique as pontuações em ordem crescente.
Células A2: B6 foram nomeadas GradeList.
As pontuações são inseridas em uma folha chamada Report Card, onde uma fórmula VLOOKUP calcula a nota.
No captura de tela abaixo, a fórmula foi copiada para a linha 6 e você pode ver a fórmula na célula C6.
Combinar VLOOKUP com MATCH
Em vez de digitar o número da coluna em uma fórmula VLOOKUP, use a função MATCH para encontrar a coluna correta na tabela de pesquisa. Isso tem alguns benefícios:
- Torna a fórmula flexível, para que seja mais fácil copiar a fórmula em uma planilha.
- Pode evitar problemas se novas colunas forem adicionadas na tabela de pesquisa, ou se as colunas de pesquisa são reorganizadas.
Este vídeo mostra as etapas e há instruções escritas para outro exemplo, abaixo do vídeo.
Seu navegador não pode mostrar este quadro. Aqui está um link para a página
VLOOKUP com correspondência para detalhes do pedido
Neste exemplo, uma fórmula VLOOKUP retornará os detalhes do pedido de uma tabela de pesquisa, com base no número de ID do pedido . Aqui está a tabela de pesquisa, chamada tblOrders.
OBSERVAÇÃO: Este exemplo está no exemplo de pasta de trabalho nº 1, na planilha chamada OrdersMATCH.
Aqui está a planilha com as fórmulas VLOOKUP. Queremos a região, a data do pedido e o valor do pedido para cada pedido, portanto, são necessárias 3 fórmulas VLOOKUP.
Se os números da coluna forem digitados na fórmula, uma fórmula diferente é necessária em cada coluna:
- Região: = VLOOKUP ($ B6, tblOrdersALL, 2,0)
- OrderDate: = VLOOKUP ($ B6, tblOrdersALL, 3,0)
- OrderAmt: = VLOOKUP ($ B6, tblOrdersALL, 4,0)
A função MATCH
Em vez de digitar o número da coluna na fórmula VLOOKUP, podemos usar a função MATCH. A função MATCH encontra a posição de um item em uma lista e retorna o número da posição.
Na captura de tela abaixo, a fórmula MATCH retorna 2 como a posição de “Região”, nas células de título (A1: D1) da tabela de pesquisa.
= MATCH ( C5, Pedidos_Todos! $ A $ 1: $ D $ 1, 0)
NOTA: Para esta técnica funcionar corretamente, o os cabeçalhos na planilha PROCV devem corresponder exatamente aos cabeçalhos da tabela de pesquisa. Para garantir uma correspondência exata, as células do cabeçalho VLOOKUP são vinculadas às células do cabeçalho da tabela de pesquisa.
Adicionar MATCH to VLOOKUP
Para adicionar a função MATCH à fórmula VLOOKUP, basta substituir o número da coluna digitada
= VLOOKUP ($ B6, tblOrdersALL, 2,0)
com a fórmula MATCH:
= VLOOKUP ($ B6, tblOrdersALL, MATCH (C5, Pedidos_Todos! $ A $ 1: $ D $ 1, 0), 0)
Copiar a fórmula VLOOKUP em
Agora, em vez de precisar de uma fórmula diferente em cada coluna, você pode copiar a fórmula e usar a mesma fórmula em todas as colunas. Em cada coluna, ele se referirá ao título da célula nessa coluna e encontrará sua posição na tabela de pesquisa.
NOTA: Se você estiver preenchendo a fórmula em colunas com formatação diferente, siga estas etapas:
- Selecione a célula com a fórmula que deseja copiar
- Aponte para a alça de preenchimento da célula selecionada (pequeno quadrado no canto inferior direito)
- Pressione o botão DIREITO do mouse e arraste até a última célula que precisa da fórmula
- Solte o botão do mouse e clique em Preencher sem formatação
Combine IF e VLOOKUP
Você pode usar uma fórmula IF com uma fórmula VLookup, para retornar valores exatos, se encontrados, e uma string vazia se não for encontrado.
Para ver as etapas de configuração da fórmula IF e VLOOKUP, você pode assistir a este breve vídeo. As instruções escritas estão abaixo do vídeo.
Para ocultar erros combinando IF com VLOOKUP, siga estas etapas:
- Na folha de fatura, na célula A8, insira o produto código A28. Se a fórmula VLookup na célula B8 tiver FALSE como o quarto argumento, o resultado será # N / A, porque não há correspondência exata para o código do produto na tabela de pesquisa.
- Enrole a fórmula VLookup com um Fórmula IF (neste exemplo, a lista de produtos foi nomeada), usando a função ISNA para verificar se há um erro # N / A:
- Pressione a tecla Enter e a célula aparecerá em branco. Como nenhuma correspondência exata foi encontrada, a fórmula VLookup retornou um # N / A, então o resultado da função ISNA é TRUE. A fórmula IF converteu isso em uma string vazia.
= IF (ISNA (VLOOKUP (A8, ProductList, 2, FALSE) ), “”, VLOOKUP (A8, ProductList, 2, FALSE))
Combine IFERROR e VLOOKUP
Obrigado a Chip Pearson por sugerir esta fórmula.
No Excel 2007, uma nova função, IFERROR, foi introduzida . Você poderia usar uma fórmula IFERROR com VLookup para verificar um valor em várias tabelas e retornar as informações relacionadas quando encontradas. Neste exemplo, três regiões, Oeste, Leste e Central, têm folhas de pedidos. Em cada folha há um intervalo nomeado – PedidosW, PedidosE e PedidosC.
Em uma planilha chamada Pedidos, você pode inserir um ID de pedido e usar um VLOOKUP com IFERROR para verificar cada intervalo nomeado e veja as informações sobre o pedido selecionado.
- Na folha do pedido, na célula B6, insira 4 como o ID do pedido. Esse pedido foi feito na região Central.
- Para simplesmente verificar a tabela da região Leste, a fórmula VLOOKUP na célula C6 seria:
= VLOOKUP (B6, PedidosE, 2, FALSO) - Pressione a tecla Enter e a fórmula VLOOKUP retorna um # N / A, porque o ID do pedido 4 não está na tabela de pedidos das regiões leste.
- Porque um pedido pode ter foi colocado em qualquer uma das três regiões, você precisa de uma fórmula que verificará cada tabela.
Se o ID do pedido não for encontrado na primeira tabela, a fórmula deve verificar a segunda tabela. Se o ID do pedido não estiver na segunda tabela, ele deve verificar a terceira tabela. Se o ID do pedido não estiver na terceira tabela, uma mensagem “Não encontrado” deve aparecer na célula. - A fórmula IFERROR permite que você verifique um valor, em seguida, especifique o que fazer se um erro for encontrado. Se você usar IFERROR com a fórmula existente, poderá mostrar “Não encontrado”, em vez do erro # N / A:
= IFERROR (VLOOKUP (B6, PedidosE, 2, FALSO), “Não encontrado”) - Para verificar todas as três tabelas, você pode seguir as fórmulas IFERROR e VLOOKUP:
Isso verifica a tabela OrdersE e se um erro for encontrado, verifica a tabela OrdersW e, em seguida, OrdersC. Se OrderID não for encontrado em nenhuma das três tabelas, a mensagem Not Found é mostrada na célula.
Você também pode verificar várias tabelas em versões mais antigas do Excel, onde IFERROR não está disponível, usando um fórmula mais longa:
VLOOKUP para valores combinados
Em algumas tabelas, pode não haver valores únicos qualquer coluna na tabela de pesquisa. Por exemplo, na tabela mostrada abaixo, Jaqueta é listada duas vezes na coluna A. No entanto, há apenas um registro para cada combinação de jaqueta e tamanho – Jaqueta Média na linha 4 e Jaqueta Grande na linha 5.
Se você precisar encontrar o preço de uma jaqueta grande, um VLOOKUP com base apenas na coluna A retornaria o preço da primeira jaqueta listada (média ) Você estaria subestimando o preço da jaqueta – vendendo por 60,00, em vez de 65,00.
Para criar valores de pesquisa exclusivos, você pode inserir uma nova coluna no lado esquerdo da tabela e usar uma fórmula para combinar o produto e o tamanho. Na célula A2, a fórmula combina o valor em B2 e a barra vertical e o valor em C2.
= B2 & “|” & C2
Copie essa fórmula até a última linha de dados, de modo que cada linha tenha um valor único na coluna A.
Observação : Em vez da barra vertical, você pode usar outro caractere que não está incluído em seus dados.
Então, em uma fórmula VLOOKUP, combine o produto e o tamanho como Lookup_value. Na célula H1, a fórmula combina o valor em F1 e a barra vertical e o valor em G1.
= VLOOKUP (F1 &” | “& G1, $ A $ 2: $ D $ 5,4, FALSO)
Nota: O preço agora está em coluna 4, em vez da coluna 3. ▲ TOP
Solucionar problemas da fórmula VLOOKUP
Sua fórmula VLOOKUP pode retornar um # N / A, mesmo que o valor que você está procurando pareça estar na tabela de pesquisa. As causas comuns para isso são:
Texto vs. número
Caracteres de espaço
Outros caracteres
Texto vs. número
Uma causa comum para esse erro é que um dos valores é um número e o outro é um texto. Por exemplo, a tabela de pesquisa pode conter “123 (texto) e o valor a ser pesquisado é 123 (um número).
- Se possível, converta o texto em números, usando um dos métodos mostrados aqui: Converter texto em números
- Se você não puder converter os dados, poderá converter o valor de pesquisa na fórmula VLOOKUP, usando uma das soluções abaixo:
- Valores de pesquisa são Texto, Tabela tem Números
- Valores de pesquisa são Números, Tabela tem Texto
- Para descobrir quais valores são texto e quais são números, consulte o número detalhado ou etapas de solução de problemas de texto
1. Os valores de pesquisa são Texto e a tabela contém Números
Se a tabela de pesquisa contém números e o valor a ser pesquisado é texto, use uma fórmula semelhante à seguinte:
= VLOOKUP (–A7, Produtos!$ A $ 2: $ C $ 5,3, FALSE)
O unário duplo (-) converte o texto em um número e funcionará corretamente mesmo se os valores de pesquisa forem números.
2. Os valores de pesquisa são números e a tabela contém texto
Se a tabela de pesquisa contém texto e o valor a ser pesquisado é numérico, use uma fórmula semelhante à seguinte:
= VLOOKUP (A7 & “”), Produtos! $ A $ 2: $ C $ 5,3, FALSO)
OU
= VLOOKUP (TEXTO (A7, “00000”) , Produtos! $ A $ 2: $ C $ 5,3, FALSO)
A função TEXTO converte um número em texto e funcionará corretamente mesmo se os valores de pesquisa forem texto. No primeiro exemplo, o operador & cria uma string de texto a partir de um número não formatado. No segundo exemplo, um número formatado com zeros à esquerda (por exemplo, 00123) corresponderia a um “número” de texto com zeros à esquerda.
Para ver as etapas para corrigir o problema VLOOKUP quando a tabela de pesquisa tem valores de texto, assista a este pequeno tutorial em vídeo.
Espaços em um valor e não no outro
Outra causa potencial para nenhum valor correspondente sendo encontrado é a diferença de espaços. Um dos valores pode conter espaços à esquerda (ou à direita ou espaços incorporados) e o outro não. Para testar os valores, você pode usar a função LEN para verificar o comprimento de cada valor.
Por exemplo: = LEN (A7) retornará o número de caracteres na célula A7. Deve ser igual ao número de caracteres na célula correspondente na tabela de pesquisa.
Se possível, remova o desnecessário espaços, e a fórmula VLOOKUP deve funcionar corretamente. Se você não puder remover os espaços, use a função TRIM em VLOOKUP, para remover espaços à esquerda, à direita ou duplicados. Por exemplo:
= VLOOKUP (TRIM (A7), ProductList, 2, FALSE)
Outros caracteres
Se a função TRIM sozinha não resolver o problema , você pode tentar uma das seguintes sugestões:
- Função SUBSTITUTE
- Função CLEAN
- Macro para remover caracteres da web
Função SUBSTITUTE
Use a função SUBSTITUTE para remover caracteres indesejados. Há um exemplo no blog Contextures: Limpar dados do Excel com TRIM e SUBSTITUTE
Função CLEAN
Outra maneira de corrigir problemas de PROCV é com a função CLEAN, que pode remover alguns caracteres indesejados do texto. Há mais informações sobre a função CLEAN nesta postagem do blog Contextures: 30 funções do Excel em 30 dias: 29 – CLEAN
Caracteres da web em um valor e não no outro
Se você copiou dados de uma página da web, ela pode conter um espaço HTML sem quebra (& nbsp) caracteres. David McRitchie escreveu uma macro para removê-los, junto com o outros caracteres de espaço
Problemas ao classificar a fórmula VLOOKUP
Uma fórmula VLOOKUP pode retornar os resultados corretos no início, mas mostra resultados incorretos se a lista de itens for classificada. Isso pode ocorrer se a referência ao valor Lookup incluir um nome de planilha. Por exemplo:
= VLOOKUP (“Formulário de pedido”! B5, Produtos! $ B $ 2: $ C $ 6,2, FALSO)
NOTA: Este problema pode ocorrer com outras funções também, como uma fórmula de pesquisa INDEX / MATCH.
Assista a este vídeo para ver as etapas para corrigir o problema e baixe o arquivo de amostra do problema de classificação VLOOKUP para acompanhar. As instruções escritas estão abaixo do vídeo.
Nomes de planilha na referência
Este tipo de referência é criado se você clicar em outra planilha durante a construção da fórmula. Assim que você fizer isso, o Excel adicionará o nome da planilha a quaisquer referências subsequentes na fórmula.
Na captura de tela acima, o vestido está na célula B9 e a célula C9 mostra o preço correto de US $ 30.
No entanto, depois de classificar os produtos de AZ, o vestido sobe para a célula B5, mas a fórmula na célula C5 continua referindo-se para a célula B9. Por causa dos nomes das planilhas nas referências, o Excel retém as referências originais, em vez de manter uma referência à linha atual. A célula C5 mostra o preço de uma camisola, em vez de um vestido. ▲ TOPO
Resolva o problema
Para resolver o problema, remova todos os nomes de planilhas desnecessários do Referências de célula VLOOKUP. Aqui está a fórmula revisada para a célula C5:
= VLOOKUP (B5, Produtos! $ B $ 2: $ C $ 6,2, FALSO)
Depois que os nomes de planilhas desnecessários forem removidos, a lista pode ser classificada com segurança e os resultados corretos serão exibidos para cada item.
Baixe os arquivos de amostra
1. Obtenha a pasta de trabalho de exemplo VLOOKUP (Excel 2007 e posterior). O arquivo compactado está no formato xlsx e não contém macros.
2. Para o Excel 2003, obtenha esta versão do arquivo de exemplo VLOOKUP. O arquivo compactado está no formato xls e não contém macros.
3. Para ver o problema que pode ocorrer ao classificar com VLOOKUP, obtenha este arquivo de amostra de problema de classificação de VLOOKUP. O arquivo compactado está no formato xlsx e não contém macros.
4. Obtenha a pasta de trabalho Consulta de preço do produto, usada no tutorial em vídeo.O arquivo compactado está no formato xlsx e não contém macros. ▲ TOPO
Mais tutoriais
Solução de problemas de número / texto da VLOOKUP
VLOOKUP de outra pasta de trabalho
HLOOKUP
ESCOLHER Função
Lookup – 2 critérios
LOOKUP
INDEX / MATCH
Comparar funções de pesquisa
Lista de funções