Excel 2007: A pesquisa de fórmula de matriz usando INDEX e MATCH está apenas olhando para a primeira linha

Question

Eu tenho uma planilha, "2011-11", com uma série de números na coluna C. Os números são cada um em uma determinada moeda, identificados por um código na coluna B. O que eu quero fazer é escrever uma única fórmula (na célula C14) que irá converter cada número (de C4: C13) em libras esterlinas (GBP), dividindo-o pela taxa de câmbio relevante, então tome a soma desses números.

As taxas de câmbio são armazenadas em uma planilha separada, "GBP". A taxa muda a cada mês para que haja uma tabela com os códigos de moeda listados verticalmente e o mês listado horizontalmente.

Eu tenho uma fórmula que, para uma única célula C4, procura corretamente a taxa de câmbio:

=INDEX(GBP!$A$4:$BE$42,MATCH($B4,GBP!$A$4:$A$42,0),MATCH(DATE(LEFT(A1,4),MID(A1,6,2),31),GBP!$A$3:$CE$3,1))

Então eu esperava que, alterando o intervalo de referência no primeiro MATCH para apontar para $B4:$B13 , inserindo a coisa toda em uma fórmula SUM e inserindo-a como uma fórmula de matriz, eu acertaria resposta:

{=SUM(C4:C13/INDEX(GBP!$A$4:$BE$42,MATCH($B4:$B13,GBP!$A$4:$A$42,0),MATCH(DATE(LEFT(A1,4),MID(A1,6,2),31),GBP!$A$3:$CE$3,1)))}

No entanto, embora esteja quase lá, isso está aplicando a taxa de câmbio da linha 4 a cada linha. Ajuda muito apreciada! Pode ser feito, ou eu preciso inserir uma coluna extra?

Algumas notas extras:

1. Eu tentei usar SUMPRODUCT em vez de SUM , mas isso não fez diferença

2. O valor de A1 é "2011-11"

Answer 1

Meu primeiro palpite foi parcialmente correto, mas no sentido errado.

Problema 1

O valor de pesquisa

DATE(LEFT(A1,4),MID(A1,6,2),31)

é baseado no valor 2011-11 e é, portanto, equivalente a

DATE(2011,11,31)

Isso dará valores inconsistentes. Para outubro, dá 31Oct11. Para novembro é de 1Dec11. Para obter valores consistentes, como o último dia de qualquer mês, use:

DATE(LEFT(A1,4),MID(A1,6,2)+1,0)

Problema 2

Se você copiar sua fórmula inicial na coluna

=INDEX(GBP!$A$4:$BE$42,MATCH($B4,GBP!$A$4:$A$42,0),
 MATCH(DATE(LEFT(A1,4),MID(A1,6,2),31),GBP!$A$3:$CE$3,1))

você recebe "#VALUE!" para todas as cópias porque A1 se torna B1, C1, etc.

Você precisa

=INDEX(GBP!$A$4:$BE$42,MATCH($B4,GBP!$A$4:$A$42,0),
 MATCH(DATE(LEFT($A$1,4),MID($A$1,6,2),31),GBP!$A$3:$CE$3,1))
                 ^ ^          ^ ^        

Problema 3

Na sua fórmula de matriz

=SUM(C4:C13/INDEX(GBP!$A$4:$BE$42,MATCH($B4:$B13,GBP!$A$4:$A$42,0),
 MATCH(DATE(LEFT(A1,4),MID(A1,6,2),31),GBP!$A$3:$CE$3,1)))}

você tem cinco intervalos dos quais deseja usar o Excel como dois arrays. Não consigo encontrar nada para sugerir o Excel se for inteligente o suficiente para adivinhar o que você quer.

O melhor que posso oferecer é definir D4 para:

=INDEX(GBP!$A$4:$BE$42,MATCH($B4,GBP!$A$4:$A$42,0),
 MATCH(DATE(LEFT($A$1,4),MID($A$1,6,2)+1,0),GBP!$A$3:$CE$3,1))

Copie esta fórmula na coluna e calcule o total com:

{=SUM(C4:C13/D4:D13)}

No entanto, eu sou um bom programador VBA para Excel; Eu não sou um usuário avançado do Excel. Outros podem ser capazes de lhe dizer como conseguir o que você procura.