VLOOKUP ou INDEX MATCH não atualiza colunas ao fazer referência à tabela dinâmica

Question

Eu estou indo maluco tentando descobrir isso. Pensei em acertar o jackpot quando aprendi sobre o INDEX-MATCH e a flexibilidade adicional sobre o VLOOKUP, mas essa flexibilidade não parece ajudar quando a fórmula faz referência a uma tabela dinâmica.

Meu problema é que preciso adicionar outra coluna à tabela dinâmica, mas, quando faço isso, todas as fórmulas VLOOKUP que fazem referência a dados à direita da nova coluna são quebradas. Posso fazer com que ele atualize automaticamente a letra da coluna na fórmula somente ao fazer referência a uma tabela normal.

Se o VLOOKUP referenciar dados de uma tabela dinâmica que eu adiciono uma coluna a ... um desastre ...

    

Answer 1

GETPIVOTDATA é a função de pesquisa nativa para tabelas dinâmicas, para que você possa usar isso em vez de VLOOKUP ou INDEX e MATCH . As strings na função GETPIVOTDATA podem ser atribuídas às suas variáveis para uma pesquisa efetiva.

    

Answer 2

Percebo que o OP já encontrou a resposta em GETPIVOTDATA , mas, para outros usuários para os quais isso não funciona, aqui está outra opção.

Se, por alguma razão, o uso do GETPIVOTDATA não funcionar para as suas necessidades - já me deparei com esse problema algumas vezes - então você ainda pode usar seu combo INDEX / MATCH com uma pequena modificação, mas ele fará isso muito mais tempo. O formato geral é =INDEX(ResultArray, MATCH(Lookup_Value, Lookup_Array, Match_Type))
Você precisa modificar ResultArray e Lookup_Array para ser dinâmico sem depender da atualização da fórmula automaticamente. Você pode fazer isso com a função OFFSET . Por exemplo, digamos que você tenha uma tabela dinâmica com os cabeçalhos na linha 4. Use OFFSET e MATCH para encontrar primeiro os cabeçalhos que você deseja.

=INDEX(OFFSET(FirstColumnOfPivotTable, 0, MATCH("Result Header", "4:4", 0)), MATCH(Lookup_Value, OFFSET(FirstColumnOfPivotTable, 0, MATCH("Lookup Header", "4:4", 0)), Match_Type))

Existem alguns problemas com esta solução:

• A seleção do intervalo a ser usado para FirstColumnOfPivotTable é fundamental. Se você escolher um intervalo estático, torne-o maior do que você jamais precisará. Seria melhor também ser dinâmico com base no tamanho da tabela dinâmica.
• Se você adicionar / remover filtros na tabela dinâmica, a linha 4 pode não ter mais os cabeçalhos e isso quebraria a fórmula
• Qualquer coisa que dependa de mais coisas em alinhamento é mais provável que quebre

GETPIVOTDATA só pode retornar um número que já está sendo exibido na tabela dinâmica. Se você deseja o total de algum subcabeçalho, mas o total não é mostrado na tabela dinâmica, a função não pode fornecê-lo a você. Se possível, modifique sua tabela dinâmica para usar GETPIVOTDATA porque é mais robusto. Se você não puder fazer isso, tente a fórmula mais longa acima e tenha cuidado apenas com a maneira como gerencia a tabela dinâmica.