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:

I tried using

`SUMPRODUCT`

instead of`SUM`

, but this made no differenceThe value of A1 is "2011-11"