Por que VLOOKUP às vezes falha

VLOOKUP é confiável, mas falha de algumas maneiras previsíveis. A maioria dos erros não é misteriosa quando você conhece as regras.

Nesta lição, você aprenderá as causas comuns de uma pesquisa que não funciona e exatamente como corrigir cada uma delas. Ao conhecê-las, você transformará os erros confusos #N/A e #REF! em correções rápidas e simples.

VLOOKUP só pode pesquisar na coluna mais à esquerda do seu intervalo da tabela e retornar valores à direita. Ele não pode pesquisar um valor e retornar algo que esteja à sua esquerda.

Se os seus identificadores estiverem na coluna C e o nome desejado estiver na coluna A, VLOOKUP não poderá retornar a um valor à esquerda. Suas opções são reorganizar as colunas para que a coluna de pesquisa seja a primeira ou usar INDEX-MATCH ou XLOOKUP, que pesquisam em qualquer direção.

O índice da coluna é contado a partir da esquerda do intervalo da tabela, não da planilha. Um erro comum é usar como número a letra da coluna da planilha.

Se o seu intervalo for C1:F10 e você quiser a coluna F, ela será a 4.ª coluna do intervalo; portanto, o índice será 4, não 6. Contar a partir da extremidade errada retorna o campo incorreto ou, se o número exceder a largura do intervalo, um erro #REF!.

Se o índice da coluna for maior que o número de colunas do intervalo da tabela, VLOOKUP retornará #REF!.

Por exemplo, pedir a coluna 5 de um intervalo com 3 colunas, A1:C10, é impossível:

Corrija isso ampliando o intervalo da tabela para incluir a coluna necessária ou corrigindo o índice para um número de coluna real dentro do intervalo.

Omitir o quarto argumento faz com que o padrão seja TRUE (aproximada). Em uma lista não ordenada, isso retorna silenciosamente um valor vizinho incorreto em vez de um erro, o que é difícil de perceber.

A correção é simples e deve se tornar um hábito: sempre adicione FALSE para pesquisas com correspondência exata.

Um valor de pesquisa "A100" não corresponderá a "A100 " com um espaço no final. Os dados importados estão repletos dessas diferenças invisíveis.

Sintomas: o valor claramente existe, mas você recebe #N/A. Limpe os dois lados com TRIM para remover espaços extras:

Se o seu valor de pesquisa for o número 100, mas a tabela armazenar os códigos como texto "100" (ou vice-versa), eles não corresponderão e você receberá #N/A.

Procure o pequeno triângulo verde ou números alinhados à esquerda, que indicam texto. Corrija convertendo os valores: envolva o texto em VALUE() para transformá-lo em número ou una uma cadeia vazia a um número com &"" para transformá-lo em texto, de modo que os dois lados tenham o mesmo tipo.

Se você esquecer de bloquear o intervalo da tabela, copiar a fórmula para baixo deslocará o intervalo para fora dos seus dados. O A1:C100 da linha 2 se torna A2:C101 na linha 3 e depois A3:C102, deixando linhas para trás no caminho.

Corrija usando referências absolutas para manter a tabela fixa enquanto apenas o valor de pesquisa muda:

Cada erro aponta para uma causa:

• #N/A - o valor não foi encontrado (incompatibilidade, espaços, tipo incorreto ou ausência real do valor)
• #REF! - o índice da coluna é maior que o intervalo ou uma célula referenciada foi excluída
• #VALUE! - um argumento é do tipo incorreto, como um índice de coluna negativo ou igual a zero
• #NAME? - o nome da função está escrito incorretamente, como VLOOKP

Relacione o erro ao seu significado e você já terá resolvido metade do problema.

Enquanto estiver depurando, você também pode envolver uma pesquisa para que os usuários vejam uma mensagem clara em vez de um erro bruto. IFERROR captura qualquer erro e retorna o seu texto no lugar.

Isso não corrige a causa subjacente, portanto use esse recurso somente depois de entender por que a pesquisa falhou. Ocultar erros cedo demais pode mascarar problemas reais nos dados.

Quando uma pesquisa apresentar um comportamento inesperado, percorra esta lista rápida:

• O valor de pesquisa está na primeira coluna do intervalo?
• O índice da coluna é contado a partir da extremidade esquerda do intervalo e está dentro da largura dele?
• Você adicionou FALSE para uma correspondência exata?
• Os dois lados têm o mesmo tipo (texto ou número) e não contêm espaços extras?
• O intervalo da tabela está bloqueado com cifrões?

Percorrer esta lista de cima para baixo resolve a grande maioria das falhas de pesquisa em poucos segundos.

As causas mais comuns e suas correções:

• Limitação da coluna esquerda - reorganize as colunas ou use INDEX-MATCH / XLOOKUP
• Índice da coluna incorreto ou maior que o intervalo - conte a partir da extremidade esquerda do intervalo e amplie-o
• FALSE ausente - sempre defina uma correspondência exata para identificadores
• Espaços e texto versus número - limpe com TRIM e converta com VALUE ou &""
• Intervalo da tabela não bloqueado - use $ para manter o intervalo no lugar

Leia o código do erro, relacione-o a uma causa e aplique a correção. Agora você tem um conjunto completo de ferramentas para fazer pesquisas confiáveis.