Fórmula genérica
(=INDEX(rng1,MATCH(1,($A1=rng2)*(B$1=rng3),0)))
Resumo
Para realizar uma pesquisa multicritério e transpor os resultados para uma tabela, você pode usar uma fórmula de matriz baseada em INDEX e MATCH. No exemplo mostrado, a fórmula em G5 é:
(=INDEX(amount,MATCH(1,($F5=location)*(G$4=date),0)))
Observe que esta fórmula é uma fórmula de matriz e deve ser inserida com control + shift + enter.
Esta fórmula também usa três intervalos nomeados: local = B5: B13, valor = D5: D13, data = C5: C13
Explicação
O núcleo desta fórmula é INDEX, que está recuperando um valor do intervalo nomeado "quantidade" (B5: B13):
=INDEX(amount,row_num)
onde row_num é resolvido com a função MATCH e alguma lógica booleana:
MATCH(1,($F5=location)*(G$4=date),0)
Neste trecho, o local em F5 é comparado com todos os locais, e a data em G4 é comparada com todas as datas. O resultado em cada caso é uma matriz de valores TRUE e FALSE. Quando essas matrizes são multiplicadas juntas, a operação matemática força os valores VERDADEIRO e FALSO para um e zeros, de modo que a matriz de pesquisa que entra em MATCH fica assim:
(1;0;0;0;0;0;0;0;0)
MATCH é configurado para corresponder a 1 como uma correspondência exata e retorna a posição para INDEX como um número de linha. O número 1 funciona para o valor de pesquisa porque a matriz agora contém apenas 1 e 0, conforme mostrado acima.
F5 e G4 são inseridos como referências mistas para que a fórmula possa ser copiada por meio da tabela sem modificação.
Transpor com colar especial
Se você só precisa transpor uma tabela uma vez, não se esqueça que você pode usar colar especial.
