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

Question

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".

Answer 1

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

In your array formula

=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.