Como corrigir N/A e #VALUE! Problemas com o Vlookup? [Mais dicas]

O VLOOKUP, apesar de sua utilidade, muitas vezes pode confundir até mesmo usuários de dados experientes. E se você esta aqui, a probabilidade é, você também precise sugestionar dados em sua profissão. a maioria de usuários de Excel dependem de VLOOKUP para uma rápida recuperação de informações. É uma ferramenta útil, mas problemas com VLOOKUP frequentemente atrapalham seu fluxo de trabalho. Há várias razões por trás disso, incluindo incompatibilidade de dados, formatação e erros de fórmula.

Portanto, seja você do setor financeiro, de marketing ou de qualquer outra indústria, nós entendemos seus desafios. Este artigo vai apresentar os problemas comuns VLOOKUP em Excel que você pode se encontrar. Então, leia sobre como aprender a corrigir-los e as melhores práticas para aprimorar suas habilidades nas planilhas.

Parte 1: 12 problemas comuns com o VLOOKUP e soluções para resolve-los

Ao trabalhar com números e fórmulas em planilhas, você pode enfrentar problemas, como questões com o VLOOKUP. Os erros comuns são #N/A e #VALUE. Vamos explorar o que causa esses erros e encontrar soluções para lidar com eles com confiança.

Problema 1: Erros #N/A devido à formatação de texto vs. número

O VLOOKUP espera consistência, portanto, qualquer incompatibilidade leva ao erro #N/A. Esse problema com o VLOOKUP no Excel geralmente ocorre quando seus valores de pesquisa não estão formatados da mesma maneira. Se a coluna de pesquisa tiver formatação inconsistente, como uma definida como texto e outra como números, o VLOOKUP pode não encontrar uma correspondência.

Solução: Converter o formato de texto a numero

Antes de usar o VLOOKUP, certifique-se de que seus valores de pesquisa e a coluna correspondente na tabela tenham o mesmo tipo de dados. Veja como mudar do formato Texto para Número:

Passo 1: Selecione a coluna que você precisa mudar na planilha de Excel.

Passo 2: Clique direito e escolha Formato de celda para abrir as configurações na janela pop-up.

Passo 3: Ir até a aba Numero, selecione Numero embaixo da seção de categoria, e escolha o formato sem zeros inicias.

Passo 4: Clique OK para aplicar as mudanças.

Problema 2: Espaços à Frente/Trás Causando Correspondências Incorretas

Um dos problemas de formatação mais comuns com o VLOOKUP ocorre quando há espaços extras presentes nos seus dados. Esses espaços podem causar incompatibilidades, levando a resultados imprecisos ou erros #N/A. Imagine que você tenha uma tabela de nomes de produtos e seus respectivos preços:

Você deseja usar o VLOOKUP para encontrar o preço de "Apple (Extra Sweet)". No entanto, há um espaço em branco no final da célula do nome do produto.

Quando você usa a fórmula =VLOOKUP("Apple (Extra Sweet)", A1:B4, 2, FALSE), o Excel retorna "#N/A". Isso ocorre porque o espaço em branco no final torna o nome do produto diferente daquele na tabela, mesmo que visualmente pareçam idênticos.

Solução 1: Usar a função de CORTAR

Remover os espaços dos valores de lookup e a matriz da tabela. Use a função CORTAR no Excel para eliminar os espaços à esquerda ou à direita.

Exemplo:

=VLOOKUP(TRIM("Apple (Extra Sweet)"), TRIM(A1:A4), 2, FALSE)

Você também pode usar a função LIMPAR em vez de CORTAR, que remove caracteres extras como caracteres não imprimíveis.

Solução 2: Remover espaços manuais

Veja como remover espaços à esquerda/à direita:

Passo 1: Selecione o produto de nome de celdas que contem espaços.

Passo 2: Ir até a aba HOME e clique Encontrar & Selecionar no painel de navegação acima.

Passo 3: A janela pop-up de Encontrar e Substituir vai se abrir. Ir até a aba de Substituir e digitar um espaço no campo de texto Encontrar que.

Passo 4: Deixar o campo de texto Substituir com em branco.

Passo 5: Clique Substituir todo para remover todos os espaços à esquerda e à direita das células selecionadas.

Problema 3: Valor de Pesquisa Não Encontrado

Problemas com o VLOOKUP podem surgir ao lidar com números decimais grandes no Excel. Suas limitações podem fazer com que esses números pareçam iguais, causando discrepâncias de arredondamento. Consequentemente, o VLOOKUP pode não corresponder com precisão a eles, resultando no erro "#N/A".

Solução: Usar a função ROUND

Aplique a função ROUND ao valor de pesquisa ou à coluna correspondente na sua tabela de referência. Especifique o número de casas decimais (por exemplo, ROUND(A1, 2) para duas casas decimais).

Problema 4: Valor de Pesquisa Não Está na Primeira Coluna da Tabela

Quando o valor de pesquisa não está na primeira coluna da tabela, surge um problema com o VLOOKUP no Excel. Isso ocorre quando os dados que você deseja recuperar não estão na coluna inicial.

Solução 1: mover o valor de pesquisa para a primeira coluna

Reorganize sua tabela, colocando o valor de pesquisa na primeira coluna. Ao fazer isso, o VLOOKUP pode encontrar e recuperar as informações necessárias. Porém, isso pode ser impraticável para conjuntos de dados complexos ou planilhas grandes.

Solução 2: Use a função ÍNDICE e COMBINAR

Para obter mais flexibilidade, combine ÍNDICE e COMBINAR:

=INDEX(array or reference, MATCH(lookup_value,lookup_array,[match_type])

Essa abordagem pode buscar valores em qualquer coluna, oferecendo mais versatilidade.

Problema 5: Incapacidade do VLOOKUP de olhar para a esquerda

Outra questão comum do VLOOKUP no Excel é sua incapacidade de procurar para a esquerda. O VLOOKUP pesquisa somente à direita, limitando sua funcionalidade.

Solução: Use a função ÍNDICE e COMBINAR

Use as funções INDEX e MATCH juntas, fornecendo uma capacidade de pesquisa mais versátil que se estende para a esquerda.

Exemplo:

Imagine que você tenha uma tabela com nomes de clientes na coluna A e seus números de contato na coluna B. Você deseja encontrar o número para "John Doe".

Solução de ÍNDICE E COMBINAR: =INDEX(B2:B10, MATCH("John Doe", A2:A10, 0))

Problema 6: Seleção Incorreta do Intervalo da Tabela

A seleção incorreta do intervalo da tabela é um problema frequente do VLOOKUP no Excel. Os usuários podem escolher acidentalmente um intervalo impróprio, levando a resultados imprecisos.

Solução: Selecione o rango de dados correto

Certifique-se de que ele cubra com precisão todas as colunas e linhas relevantes. Ao copiar a fórmula, considere usar referências absolutas ($) para bloquear o intervalo. Você também pode usar o argumento range_lookup. Defina-o como VERDADEIRO para correspondências aproximadas dentro de uma tabela ordenada, mas FALSO para correspondências exatas (padrão).

Exemplo:

Você deseja encontrar o desconto para o ID do produto "XYZ789" em uma tabela com ID na coluna A, desconto na coluna C e dados que abrangem as linhas 2-15.

• VLOOKUP incorreto (referências absolutas ausentes): =VLOOKUP("XYZ789", A2:C10, 3, FALSE) (pode retornar resultados incorretos)
• VLOOKUP correto: =VLOOKUP("XYZ789", $A$2:$C$15, 3, FALSE)

Problema 7: Valor de Pesquisa Fora do Intervalo (Menor que o Mínimo)

Você recebe um erro quando seu valor de pesquisa é menor que o menor valor na primeira coluna do intervalo de pesquisa. Problemas com VLOOKUP acontece porque se procura uma correspondência exata. Se o seu valor de pesquisa estiver além dos valores na coluna de pesquisa, ele não poderá encontrar uma correspondência, resultando no erro #N/A.

Solução: Verifique por erros de entrada de dados

Assegure que o valor do lookup este dentro do rango definido. Verifique duas vezes os valores mínimos e máximos permitidos para resultados exatos.

Exemplo:

Suponha que você tenha os seguintes dados:

Se você usar VLOOKUP(5, A2:B3, 2, TRUE), receberá o erro #N/A porque 5 é menor que o valor mínimo (10) na coluna Product ID.

Lookup_value certo: Use = VLOOKUP(10, A2:B3, 2, TRUE) para encontrar o preço para a identificação do produto 10, que é $100.

Problema 8: A ordem da coluna de pesquisa não é crescente

Problemas no formato VLOOKUP, como o erro #N/A, surgem quando a ordem não é crescente, causando imprecisões nos resultados. Isso também acontece se você estiver usando a função VLOOKUP do Excel e uma correspondência aproximada (definindo range_lookup como TRUE).

Solução 1: Organize a coluna Lookup

Organizar a coluna lookup em ordem ascendente. Isso garante que o VLOOKUP possa procurar pelo valor de pesquisa, começando pelo menor valor e avançando.

Solução 2: Usar uma correspondência exata

Definir o argumetno range_lookup a FALSO. Isso força o VLOOKUP a procurar apenas por uma correspondência exata, independentemente da ordem das colunas.

Problema 9: O valor de pesquisa excede o limite de caracteres

Esse problema com o VLOOKUP no Excel ocorre quando o valor que você está tentando corresponder excede 255 caracteres de comprimento. Ele aplica o valor do lookup e qualquer valor dentro da matriz você esta procurando.

Solução: Valor abreviado / Use ÍNDICE e COMBINAR

Considere encurtar o valor de pesquisa ou usar as funções ÍNDICE e COMBINAR.

Problema 10: Valor inválido para col_index_num

No VLOOKUP, o terceiro argumento é o Índice da Coluna. Ele aponta para a coluna específica para recuperação de valor ao encontrar uma correspondência. Problemas com VLOOKUP também acontecem cuando o argumetno col_index_num tem problemas. Lídia ao erro #VALUE! que acontece se:

• Texto: VLOOKUP espera um valor numérico para este argumento.
• Menos de 1: O índice mínimo da coluna é 1 (referente à própria coluna de pesquisa).

Solução: Correção de funções aninhadas

Certifique-se de que “col_index_num” seja um número válido maior que 0 e corresponda à coluna da qual você deseja recuperar os dados. Por exemplo, se você tiver dados nas colunas A a C e quiser buscar informações da coluna B, defina “col_index_num” como 2.

Problema 11: Referências incorretas a células ou erros de digitação na fórmula

O VLOOKUP não está funcionando por outro motivo relacionado a referências de células ou à fórmula. Esse erro comum no Excel pode levar a outros erros como #N/A (valor não encontrado) e #VALUE! (argumento inválido), ou #REF! (referência inválida).

Solução: Verificar duas vezes referencias de celdas

Manualmente verifique cada referencia em sua formula. Identifique quaisquer erros de digitação ou inconsistências nas referências de células, nomes de funções ou valores de pesquisa. Se a fórmula precisar permanecer fixa independentemente de onde for copiada, converta as referências relativas em absolutas usando $.

Problema 12: Referências circulares que causam erros

Uma referência circular ocorre quando uma fórmula se refere direta ou indiretamente à sua célula. Isso cria um loop infinito que impede o cálculo.

Solução: Romper o Loop

Reestruture as fórmulas para evitar referências circulares. Você também pode usar diferentes funções or re-organizar as cálculos.

Parte 2: Dicas e as melhores praticas para o uso de VLOOKUP

O VLOOKUP é uma função poderosa do Excel para recuperar dados de uma tabela para outra, mas tem limitações. Aqui há algumas dicas e as melhores práticas para assegurar você esta usando Excel eficientemente:

Use Repairit para reparação de arquivos

Excel é uma ferramenta poderosa, mas as vezes coisas dão errado. Problemas com VLOOKUP podem bagunçar sua planilha. O arquivo pode se recusar a abrir ou mostrar dados corrompidos, causando frustração. Um arquivo corrompido pode atrapalhar a fórmula, resultando em dados imprecisos ou células em branco.

Se isso acontece, Wondershare repairit pode corrigir seus documentos danificados, incluindo arquivos de Excel. Quer tenha sido corrompido devido a uma queda de energia ou malware, este aplicativo pode ajudar a recuperar seu arquivo de forma segura e rápida. Siga estes passos para consertar erros de corrupção de Excel:

Passo 1: Download e instalar Repairit em seu computador. Depois de configurar o aplicativo, vá para Mais tipos de reparo no painel de navegação do lado esquerdo e selecione Reparo de arquivos.

Passo 2: Clique + Adicionar para escolher um arquivo de Excel para seu armazenamento local. Você também pode carregar vários arquivos do Excel para reparo em lote.

Passo 3: Clique no botão de Reparar na esquina direita da tela para começar o processo.

Passo 4: Pre-visualizar os resultados usando o ícone de pre-visualização de arquivo antes de guardar.

Passo 5: Clique Salvar ou Salvar todo para exportar o documento corrigido de Excel. Escolha o local de exportação e o aplicativo salvará seus arquivos, mostrando o progresso na tela.

Explore XLOOKUP

Se você esta usando Excel 365 ou posterior, considere XLOOKUP É mais versátil, lida com várias tabelas e até suporta caracteres curinga para correspondências parciais.

Adote a verificação de erros e a formatação condicional

Use funções como SEERRO para destacar possíveis problemas com formatação condicional. Isso torna suas planilhas do Excel mais fáceis de entender e resolver problemas.

Experimente uma porção pequena de dados

Teste suas fórmulas de VLOOKUP em uma pequena amostra de dados antes de aplica-las a todo o conjunto de dados. Isso ajuda a identificar erros e garante que a fórmula funcione conforme o esperado antes de investir tempo e esforço em aplicações mais amplas.

Fixe o intervalo de sua tabela com referências absolutas

Use referências absolutas (por exemplo, $A$1) para o intervalo da tabela em sua fórmula VLOOKUP. Ele assegura que ele permaneça ancorado no local correto, mesmo quando copiado ou arrastado.

Assegure o Sensibilidade do Caso com EXATO

Use a função de EXATO dentro VLOOKUP para garantir correspondências precisas se seus dados forem sensíveis a maiúsculas e minúsculas. Isso previne resultados inesperados devido a diferenças de capitalização.

Ative correspondências aproximadas

Defina o argumento de range_lookup em VLOOKUP para VERDADEIRO se você precisa encontrar uma correspondência aproximada quando o valor exato não esta presente. No entanto, tenha cautela, pois isso pode levar a resultados indesejados se não for usado com cuidado.

Considerações Finais

Para concluir, você precisa entender os problemas do VLOOKUP ao lidar com dados. Saber dos problemas comuns ajuda você a enfrentar quaisquer desafios que surgirem pelo caminho.

Até agora, você tem uma ideia de como resolver o problema mais comum no VLOOKUP do Excel. Explorar estas soluções assegura uma experiência sem esforço para você. adicionalmente, considerando soluções avançadas aprimora eficiência no tratamento de tarefas das planilhas. O Wondershare Repairit se destaca por resolver problemas relacionados a arquivos de forma eficaz. Então, mantenha-se praticando suas habilidades nas planilhas seguro que você pode corrigir documento corruptos facilmente.