Fórmula genérica
=IF(VLOOKUP(id,data,1,TRUE)=id, VLOOKUP(id,data,col,TRUE), NA())
Resumo
Com grandes conjuntos de dados, a correspondência exata VLOOKUP pode ser dolorosamente lenta, mas você pode tornar a VLOOKUP mais rápida usando dois VLOOKUPS, conforme explicado abaixo.
Notas:
- Se você tiver um conjunto menor de dados, essa abordagem é um exagero. Use-o apenas com grandes conjuntos de dados quando a velocidade realmente for importante.
- Você deve classificar os dados por valor de pesquisa para que esse truque funcione.
- Este exemplo usa intervalos nomeados. Se você não quiser usar intervalos nomeados, use referências absolutas.
PROCV de correspondência exata é lento
Quando você usa PROCV no "modo de correspondência exata" em um grande conjunto de dados, pode realmente diminuir o tempo de cálculo em uma planilha. Com, digamos, 50.000 registros, ou 100.000 registros, o cálculo pode levar minutos.
A correspondência exata é definida fornecendo FALSE ou zero como o quarto argumento:
=VLOOKUP(val,data,col,FALSE)
O motivo de PROCV neste modo ser lento é porque ele deve verificar cada registro no conjunto de dados até que uma correspondência seja encontrada. Isso às vezes é chamado de pesquisa linear.
VLOOKUP de correspondência aproximada é muito rápido
No modo de correspondência aproximada, VLOOKUP é extremamente rápido. Para usar VLOOKUP de correspondência aproximada, você deve classificar seus dados pela primeira coluna (a coluna de pesquisa) e, em seguida, especificar TRUE para o quarto argumento:
=VLOOKUP(val,data,col,TRUE)
(PROCV é verdadeiro, o que é um padrão assustador, mas isso é outra história).
Com conjuntos de dados muito grandes, mudar para uma correspondência aproximada de VLOOKUP pode significar um aumento dramático na velocidade.
Então, acéfalo, certo? Basta classificar os dados, usar a correspondência aproximada e pronto.
Não tão rápido (heh).
O problema com VLOOKUP no modo "correspondência aproximada" é o seguinte: VLOOKUP não exibirá um erro se o valor de pesquisa não existir. Pior, o resultado pode parecer completamente normal, embora esteja totalmente errado (veja os exemplos). Não é algo que você queira explicar ao seu chefe.
A solução é usar VLOOKUP duas vezes, ambas no modo de correspondência aproximada:
=IF(VLOOKUP(id,data,1,TRUE)=id, VLOOKUP(id,data,col,TRUE), NA())
Explicação
A primeira instância de VLOOKUP simplesmente procura o valor de pesquisa (o id neste exemplo):
=IF(VLOOKUP(id,data,1,TRUE)=id
e retorna TRUE apenas quando o valor de pesquisa é encontrado. Nesse caso,
a fórmula executa VLOOKUP novamente no modo de correspondência aproximada para recuperar um valor dessa tabela:
VLOOKUP(id,data,col,TRUE)
Não há perigo de um valor de pesquisa ausente, uma vez que a primeira parte da fórmula já foi verificada para ter certeza de que está lá.
Se o valor de pesquisa não for encontrado, a parte "valor se FALSO" da função IF é executada e você pode retornar qualquer valor que desejar. Neste exemplo, usamos NA () e retornamos um erro # N / A, mas você também pode retornar uma mensagem como "Faltando" ou "Não encontrado".
Lembre-se: você deve classificar os dados por valor de pesquisa para que esse truque funcione.
