VLOOKUP não interpolando todas as células?

Question

Minha fórmula VLOOKUP não está interpolando todas as células

    AD           AE   AF
11-6-2014 15:20 90,4 #N/A
11-6-2014 15:21 89,1 #N/A
11-6-2014 15:22 90,4 Good
11-6-2014 15:23 89,1 Good

A coluna AF é minha fórmula: (em holandês) = AE4- (VERT.ZOEKEN (AD4; $ AL $ 3: $ AM $ 39714; 2; WAAR) -1000) (tentativa de tradução em inglês) = AE4- (VLOOKUP ( AD4; $ AL $ 3: $ AM $ 39714; 2; True) -1000)

Isso faz parte da coluna da matriz:

        AL        AM
11-6-2014 15:22 1026,9
11-6-2014 15:23 1027,3
11-6-2014 15:24 1027,2
11-6-2014 15:25 1026,9

Meus dados (1026.9) em 11-6-2014 15:22 são encontrados. Mas eu quero que este valor seja interpolado para 11-6-2014 15:20 e 11-6-2014 15:21 também. Afaik deve apenas procurar a data mais próxima e gerar o valor na matriz, mas isso não acontece.

Alguma idéia?

    

Answer 1

Essa é a maneira como o VLOOKUP foi projetado para funcionar quando o parâmetro Range Lookup está definido como TRUE . Ele retornará o valor para o valor mais próximo menor ou igual ao valor de pesquisa. Se nenhum valor atender a esse critério, será retornado um erro.

Algumas opções para você:

1. Defina uma exceção para os valores abaixo do limite inferior e deixe VLOOKUP manipular o restante. Para fazer isso, apenas envolva sua função em uma função IFERROR .

   =IFERROR(AE4-(VLOOKUP(AD4;$AL$3:$AM$39714;2;True)-1000);AE4-$AM$3)
   

   Isso padroniza todas as pesquisas de produção de erros para o primeiro valor na coluna AM .

2. Se, em vez disso, você quiser retornar os dados para a data mais próxima, não apenas o mais próximo que for menor ou igual ao seu valor de pesquisa, você poderá usar algo diferente de VLOOKUP . A fórmula de matriz abaixo retornará o valor de AM para a data mais próxima. Os pontos intermediários entre as datas são mapeados para a data posterior.

   =AE4-INDEX($AM$3:$AM$39714,MAX(IF(ABS($AL$3:$AL$39714-AD4)=MIN(ABS($AL$3:$AL$39714-AD4)),ROW($AL$3:$AL$39714)-2,-1)))
   

   Esta é uma fórmula de matriz, portanto, ela deve ser digitada pressionando Ctrl + Deslocar + Inserir para funcionar corretamente. Ele aparecerá na barra de fórmulas em chaves, se isso for feito corretamente.

   Com matrizes longas como a sua ( n = 39,712), as fórmulas de matriz podem ser computacionalmente lentas e podem sobrecarregar sua planilha. Então, leve isso em consideração ao decidir qual método usar.