Excel 2007: Array formula lookup using INDEX and MATCH is only looking at first row

I have a worksheet, "2011-11", with a series of numbers in column C. The numbers are each in a given currency, identified by a code in column B. What I want to do is write a single formula (in cell C14) that will convert each number (from C4:C13) into pounds sterling (GBP) by dividing it by the relevant exchange rate, then take the sum of these numbers.

The exchange rates are stored on a separate worksheet, "GBP". The rate changes each month so there is a table with the currency codes listed vertically and the month listed horizontally.

I have a formula which, for a single cell C4, correctly looks up the exchange rate:

``````=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))
``````

So I had hoped that by changing the reference range in the first `MATCH` to point to `\$B4:\$B13`, inserting the whole thing into a `SUM` formula, and entering it as an array formula, I would get the right answer:

``````{=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)))}
``````

However, whilst it is nearly there, this is applying the exchange rate for row 4 to each row. Help much appreciated! Can it be done, or do I need to insert an extra column?

A couple of extra notes:

1. I tried using `SUMPRODUCT` instead of `SUM`, but this made no difference

2. The value of A1 is "2011-11"

-
I have not looked very carefully at your problem but I notice that your formula is full of \$s which means do not adjust the target. Could this be your problem? – Tony Dallimore Dec 5 '11 at 16:54
@TonyDallimore: It could but I don't think so. The \$s are there on purpose and mostly refer to the lookup array (which is static). – Michael Pavey Dec 5 '11 at 17:02

My first guess was partially correct but the wrong way round.

Problem 1

The look up value

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

is based on the value 2011-11 and is therefore equivalent to

``````DATE(2011,11,31)
``````

This will give inconsistent values. For October it gives 31Oct11. For November is gives 1Dec11. To get consistent values, such as the last day of any month use:

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

Problem 2

If you copy your initial formula down the column you

``````=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))
``````

you get "#VALUE!" for all the copies because A1 becomes B1, C1, etc.

You need

``````=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))
^ ^          ^ ^
``````

Problem 3

``````=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)))}
``````

you have five ranges of which you want Excel to use two as arrays. I cannot find anything to suggest Excel if clever enough to guess that this what you want.

The best I can offer is to set D4 to:

``````=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))
``````

Copy this formula down the column and calculate the total with:

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

However, I am an adequate to good Excel VBA programmer; I am not a Excel power user. Others may be able to tell you how to achieve what you seek.

-
+1 Nice analysis. I disagree with the last statement, though. – Rachel Hettinger Dec 6 '11 at 5:52
@Rachel Hettinger. Thank you for the +1. I have seen power users achieve amazing things with a few key strokes so I cannot claim to be one of there number. I only learnt VBA because it was the only programming language my then employer permitted. I have not been a professional programmer for 35 years. My major skill today is perseverance. – Tony Dallimore Dec 6 '11 at 9:52
@TonyDallimore: Thanks so much, this is really very helpful. And apologies, I think I ought to have removed some of the irrelevant complexity from the problem before posting, to save you from having to deal with my sloppy coding (problem 1 in particular -- for which, thanks for a great solution). Re problem 2, I never got as far as copying it down (have amended my original question to clarify that I was seeking to put the whole thing in a single cell) -- but you're quite right to point out that flaw :) Re 3, too bad but perhaps I was just being overambitious! – Michael Pavey Dec 6 '11 at 10:12
@Michael Pavey. Re Problem 2. My understanding of array formula is that Excel is only doing in the background what you could do on the sheet. This understanding implies: if you cannot copy the simple formula down the column, Excel cannot copy the array formula down the column. – Tony Dallimore Dec 6 '11 at 10:52