Multi-Currency Workbooks: January Was 132,549.03 in February and 134,880.05 in March, and Not One Invoice Changed

The January pack was built on 2 February and reported 132,549.03 of revenue. In the middle of March somebody opened the same file to answer a question about a different month, updated the three rate cells in the corner because they were obviously stale, and saved. January now read 134,880.05.

No invoice had been raised, cancelled, credited or amended. Eight invoices in January, eight invoices in March, the same eight numbers in the same eight rows. The month moved by 2,331.02 because the workbook did not store an exchange rate — it stored a cell reference to whatever exchange rate happened to be in the corner when you looked.

The number that does not move is 132,197.28. That is the month converted at the rate that applied on the day each invoice was raised, and it will be 132,197.28 next month, next year, and in the audit file. Everything below is about getting that number, keeping it, and being able to say where it came from.

What this covers. INDEX, MATCH, VLOOKUP, SUMIFS, SUMPRODUCT, ROUND and EOMONTH work in every version this century. XLOOKUP and LET need Microsoft 365 or Excel 2021; every formula that uses them here has an INDEX/MATCH equivalent given beside it. Nothing here needs Power Query, though section 10 says where it earns its place.

1) The Cell in the Corner

The model was not stupid. It was three cells — H1 holding 1.1998 for sterling, H2 holding 0.9182 for dollars, H3 holding 0.005844 for yen — and one formula copied down a column:

=IF(C2="EUR",D2,D2*IF(C2="GBP",$H$1,IF(C2="USD",$H$2,$H$3)))

Those three rates are the rates published on 27 January, which is what the bank's page showed on the morning the pack was built. Applied to the eight invoices they give:

The single-rate column is right on exactly three rows: the two euro invoices, which need no rate at all, and INV-2047, which happens to be dated 27 January. Everything else is converted at a rate that did not exist on the day the sale was made. The error is 351.75 — 0.27% of the month, small enough that nobody queries it and large enough to be wrong.

Then March happened. The rate cells were refreshed to the 24 February publication (1.2350, 0.9312, 0.005925) and the same column recalculated to 134,880.05. A closed month is not supposed to have a recalculation. That is the actual defect here, and it is worse than the 351.75: a figure that changes when you open the file cannot be reconciled to anything, because whatever it is reconciled against was captured on a different day.

🎯 Scenario: Before writing a single lookup, ask the diagnostic question — if I open this file in a year, does January still say what the pack said? If the answer depends on a cell somebody might refresh, the month is not closed, whatever the accounting system says.

2) Four Rates, and Which One the Number Wants

"The rate" is four different things, and most multi-currency arguments are two people each using a different one.

The transaction-date rate — the rate on the day the invoice was raised. This is what revenue is measured at, and once measured it never changes. INV-2041 is 21,564.80 forever.

The monthly average rate — one rate for the whole period, usually the average of the published rates in it. For January those averages are 1.1852, 0.9244 and 0.005889, and the month comes out at 132,353.46. That is 156.18 away from the transaction-date answer, and it is a legitimate approximation when rates have not moved much — which is a judgement someone has to make and write down, not a default.

The closing rate — the rate at the balance-sheet date. This is what unsettled foreign-currency balances get retranslated at, not revenue. Section 8.

The contract rate — a rate fixed in the contract or bought as a forward. When one exists it beats all three of the above, because it is the rate the cash will actually convert at.

The single-rate model in section 1 is not any of these four. It is the closing rate applied to revenue, which is the one combination the list above never produces.

3) Which Way Round Is the Rate?

Half of all currency errors are a division that should have been a multiplication, and they are invisible because the answer is still a plausible-looking number.

A quoted rate is always units of one currency per one unit of another, and the quote convention differs by pair and by data source. The rate table here is written EUR per one unit of foreign currency, so the conversion is a multiplication:

=D2 * rate          rate is EUR per 1 GBP → 18,400 × 1.1720 = 21,564.80

If your source quotes the other way — foreign currency per one EUR, which is how most banks show it for the euro — the same job is a division:

=D2 / rate          rate is GBP per 1 EUR → 18,400 / 0.8532 = 21,565.87

(0.8532 is 1/1.1720 rounded to four places; the 1.07 gap between the two answers is nothing but that rounding, and it is a real reason to store rates in the direction you were given them rather than storing reciprocals of them.)

The check takes two seconds and never fails: one pound is worth more than one euro, so a sterling invoice must produce a bigger euro number. 18,400 GBP → 21,564.80 EUR, bigger, correct. If it came out at 15,700, you inverted the rate. One yen is worth much less than one euro, so 1,480,000 JPY must produce a much smaller number: 8,689.08, correct. If yen came out in the millions, you inverted that one.

🎯 Scenario: Add a column headed Direction to the rate sheet holding the literal text EUR per 1 unit, and a check cell =IF(AND(F2>D2)=(C2="GBP"),"ok","CHECK") on the sterling rows. The text is for the human; the formula is for the day somebody pastes in a feed quoted the other way round.

4) The Rate as of the Invoice Date

The rate table lives on a sheet called Rates, dates in A2:A9 ascending, currencies across B1:D1:

It publishes Mondays. INV-2044 is dated Friday 16 January, INV-2046 Friday 23 January, and neither date is in the table — so an exact-match lookup returns #N/A for a quarter of the ledger. What you want is the last rate published on or before the invoice date, and there are three ways to say that.

MATCH with match type 1, which works everywhere:

=MATCH(B2,Rates!$A$2:$A$9,1)

For 16 January that returns 2 — the 13 January row — because type 1 means the largest value less than or equal to the lookup value. It is also the default, which is why =VLOOKUP(B2,Rates!$A$2:$D$9,2) with no fourth argument works here and is a catastrophe everywhere else.

XLOOKUP with search order -1 and match mode -1:

=XLOOKUP(B2,Rates!$A$2:$A$9,Rates!$B$2:$B$9,,-1)

The fifth argument -1 is exact match or next smaller item, which is the same rule stated out loud instead of implied by a bare 1.

LOOKUP(2,1/...) for the version that also filters on something else, which is section 5.

All three share one requirement that none of them announce: the dates must be sorted ascending. Sort the rate sheet by currency, or paste a feed that arrives newest-first, and MATCH(...,1) does not error — it returns a row, confidently, and it is the wrong row. This is the single most expensive habit in this whole article, and the guard is one cell:

=IF(SUMPRODUCT(--(Rates!A3:A9<Rates!A2:A8))>0,"RATES OUT OF ORDER","")

And the other guard, for a date before the table starts:

=IFNA(INDEX(...),"NO RATE")

Never IFERROR(...,0) here. A zero rate turns an invoice into nothing at all and the total still adds up, which is how 24,600 dollars leaves a ledger without anybody noticing.

🎯 Scenario: A backdated credit note lands with an invoice date of 30 December, before the first row of the rate table. MATCH(...,1) returns #N/A, the IFNA puts "NO RATE" in the cell, and the total goes to #VALUE! — loudly, on the day it happens, which is exactly what you want. With IFERROR(...,0) it would have been silently free.

5) Two Keys at Once: Currency and Date

The invoice needs a rate chosen by two things, and how you write it depends on which shape your rate table is in.

Wide table — one row per date, one column per currency, as above. The row comes from the date, the column from the currency, and INDEX takes both:

=INDEX(Rates!$B$2:$D$9, MATCH(B2,Rates!$A$2:$A$9,1), MATCH(C2,Rates!$B$1:$D$1,0))

Note the two different match types on purpose: 1 down the dates because you want the nearest earlier date, 0 across the headers because "USD" must mean USD and nothing else. Getting those the wrong way round is a bug that produces numbers.

Wrapped up with the euro case and the rounding, the production formula is:

=IF(C2="EUR", D2,
   ROUND(D2 * INDEX(Rates!$B$2:$D$9,
                    MATCH(B2,Rates!$A$2:$A$9,1),
                    MATCH(C2,Rates!$B$1:$D$1,0)), 2))

Long table — one row per date and currency, three columns: Date, Currency, Rate. This is the shape every rate feed arrives in, and the shape that survives a fifth currency being added. Sorted by date ascending, the last row matching both conditions is the one you want:

=XLOOKUP(1, (Rates!$B$2:$B$97=C2)*(Rates!$A$2:$A$97<=B2), Rates!$C$2:$C$97, , 0, -1)

The (...)*(...) multiplies two arrays of TRUEs and FALSEs into 1s and 0s, so the lookup array holds 1 on every row that is both the right currency and not in the future, and the final -1 says search from the bottom, which finds the most recent of them. Before XLOOKUP existed, the same idea was written:

=LOOKUP(2, 1/((Rates!$B$2:$B$97=C2)*(Rates!$A$2:$A$97<=B2)), Rates!$C$2:$C$97)

1/0 is #DIV/0!, so the non-matching rows become errors, LOOKUP ignores errors, and 2 is bigger than every 1 left standing — so it lands on the last one. It is ugly and it works in Excel 97.

With LET, so the row is found once rather than twice and the formula says what it means:

=LET(row, MATCH(B2,Rates!$A$2:$A$9,1),
     col, MATCH(C2,Rates!$B$1:$D$1,0),
     rate, INDEX(Rates!$B$2:$D$9,row,col),
     IF(C2="EUR", D2, ROUND(D2*rate,2)))

🎯 Scenario: The finance system starts sending CHF as well. On the wide table that is a new column, a re-pointed $B$2:$D$9 in every formula, and a header row to keep in step. On the long table it is more rows, and nothing to change. Pick the long table if the currency list is not final.

6) Where to Round, and What It Costs

Two ways to add up a converted ledger, and they are not the same number.

Round each line, then add. ROUND(D2*rate,2) down column F, then =SUM(F2:F9) → 132,197.28.

Add the products, round once. =SUMPRODUCT(D2:D9,F2:F9) over the raw rates → 132,197.275, which displays as 132,197.28 and is not 132,197.28.

Here the gap is half a cent, and it comes from one row: 9,250 × 1.1785 = 10,901.125, the only line in the month that lands on a half. On eight invoices this is a curiosity. On four thousand it is the difference between a ledger that ties to the bank and one that is out by an amount nobody can find, because the discrepancy is not in any single row.

The rule that ends the argument: round where the money is. A euro amount that will be posted, invoiced or paid is rounded to two decimals at the point it becomes a euro amount, and every total after that is a sum of already-rounded numbers. Never round the rate to make the total tidy — 1.1720 shortened to 1.17 moves 18,400 GBP by 36.80.

And yen has no minor unit. A JPY-denominated presentation rounds to zero decimals, so a total of 1,480,000 stays 1,480,000 and never acquires a ".00" that implies a precision the currency does not have. Custom formats do this per column: #,##0.00 "EUR" beside #,##0 "JPY".

7) The Total That Is Denominated in Nothing

=SUM(D2:D9) returns 1,600,140. It is a real number, it is arithmetically correct, and it means nothing: 92.5% of it is yen, and the rest is three other currencies added to yen as though a pound were a yen.

Every total on a mixed-currency ledger is either converted first or split by currency. Split, with SUMIFS:

=SUMIFS($D$2:$D$9, $C$2:$C$9, $H5)      GBP 41,950 · USD 56,500 · JPY 1,480,000 · EUR 21,690

Converted, over the rate column F:

=SUM($F$2:$F$9)                          132,197.28 EUR
=SUMIFS($F$2:$F$9, $C$2:$C$9, "GBP")     49,623.07 EUR of it is sterling

The sterling figure is 21,564.80 + 10,901.13 + 17,157.14 = 49,623.07 — 37.5% of the month, from three invoices, at rates that moved 5.4% between January and late February. That concentration is the reason any of this matters; a ledger 3% foreign can round its way through life.

🎯 Scenario: Somebody asks for "total sales this month" and the sheet has a mixed-currency amount column. The correct first answer is a question — in what currency? — and the correct second is two figures: the reporting-currency total and the split that produced it. A single number off a mixed column is not a smaller answer, it is a wrong one.

8) Settlement: the 1,251.37 That Is Not Sales

Revenue is fixed at the invoice date. The cash is not, and the difference has to go somewhere.

Four of the eight invoices were settled in February, and each converted at the rate on the day the money arrived:

That 1,251.37 is a realised exchange gain. It is not a sale, it did not come from a customer paying more, and it must not touch the revenue line — INV-2041 is still a 21,564.80 sale, and 1,043.28 of gain arrived six weeks later because sterling moved 4.8%.

The two still-open sterling invoices get retranslated at the closing rate — 1.2350 on 24 February, the last publication on or before 28 February. Put any date inside the reporting month in $J$1 and EOMONTH finds the month end, so the formula does not carry a hardcoded 28/02 that will be wrong in March and catastrophically wrong every leap year:

=INDEX(Rates!$B$2:$D$9, MATCH(EOMONTH($J$1,0),Rates!$A$2:$A$9,1), MATCH(C2,Rates!$B$1:$D$1,0))

INV-2044 goes 10,901.13 → 11,423.75 (+522.62) and INV-2047 goes 17,157.14 → 17,660.50 (+503.36), so 1,025.98 of unrealised gain sits on the receivable, to be reversed and re-struck next month end.

Total currency effect on the January cohort: 1,251.37 realised plus 1,025.98 unrealised = 2,277.35, all of it below the revenue line, none of it changing the 132,197.28.

Now compare that with what the corner-cell model did in March. Its 134,880.05 is 2,682.77 above the truth, and 2,682.77 − 2,277.35 = 405.42. That 405.42 is the model retranslating invoices that had already been settled in cash — INV-2041 and INV-2043 and INV-2046 were converted at a February rate months after the euros had actually landed in the bank. The single-rate model does not just get the rate wrong; it applies a closing rate to transactions that no longer have any currency exposure at all.

9) Constant Currency: the Month That Fell Twice as Far

February invoiced GBP 41,200, USD 55,300, JPY 1,455,000 and EUR 21,400. January invoiced GBP 41,950, USD 56,500, JPY 1,480,000 and EUR 21,690. Every foreign column is down.

Translated at each month's own average rates, the euro totals are 132,353.46 and 131,594.33 — down 0.6%, which reads in a board pack as flat.

Translate both months at the same rates — January's averages, held constant — and February is 129,918.06 against 132,353.46: down 1.8%.

Reported:          132,353.46 → 131,594.33     −0.6%
Constant currency: 132,353.46 → 129,918.06     −1.8%
Difference:                       1,676.27      the exchange rate

1,676.27 of February's euro revenue is the euro, not the customers. Sterling gained 3.3% between the two monthly averages and covered up two-thirds of a real decline. This is the single most useful thing a multi-currency workbook can produce that a single-currency one cannot, and it is one extra column: the same amounts, multiplied by a frozen base rate instead of the live one.

=IF(C2="EUR", D2, ROUND(D2 * INDEX(Base!$B$2:$D$2,,MATCH(C2,Base!$B$1:$D$1,0)), 2))

🎯 Scenario: Any growth figure that crosses a currency needs both numbers side by side, and the base rate named — "at January 2026 average rates". Without the base named, "constant currency" is a claim rather than a calculation, and two teams will pick two bases and produce two growth rates from the same ledger.

10) Freezing the Past

Everything above collapses back into one habit: the rate belongs on the row, not in the corner.

Give the ledger three stored columns — Rate Used, Rate Date, EUR Amount — and populate them once, when the invoice is entered. Then:

• The month cannot restate itself, because nothing recalculates against a moving cell.
• Every euro figure is auditable in one click: the rate is right there, and so is the date it came from.
• A rate published wrong and corrected next week does not silently rewrite last week's revenue. Correcting it becomes a decision somebody makes and records.

Two ways to freeze. Paste-values the computed rate column once the period closes — crude, effective, and the one that a reviewer can verify without understanding the model. Or write the rate at entry and let the formula read the stored rate: =ROUND(D2*G2,2) where G2 is a typed or looked-up-once number, not a live lookup.

Power Query earns its place at exactly one point here: pulling a rate feed in, unpivoting it into the long Date/Currency/Rate shape, and appending it to a stored rate history rather than replacing it. A rate table that is appended to keeps every rate it has ever published, which is what makes any of the lookups in section 4 answerable a year later.

11) Twelve Traps

1. A rate cell that gets refreshed. The defect this whole article is about: a closed period whose value depends on when you opened the file.
2. MATCH(...,1) on unsorted dates returns a wrong row without an error. Guard it, or sort it on every import.
3. IFERROR(...,0) on a missing rate converts a real invoice to zero and keeps the total looking sensible. Use IFNA and let it fail.
4. Inverted rates. Both directions produce a number. Only the sanity check in section 3 catches it — and only if somebody does it.
5. Rounding the rate instead of the amount. 1.17204 rounded to 1.17 moves 18,400 GBP by 37.54.
6. Yen with two decimal places. JPY has no minor unit; a ".00" on a yen figure is a formatting error that reads as false precision.
7. Summing a mixed-currency column. 1,600,140 is arithmetic without meaning. Convert first or split by currency.
8. Retranslating settled items. Once the cash has landed, the exposure is gone; retranslating it at a later rate invents 405.42 out of nothing.
9. Exchange gains in revenue. The 1,251.37 is a treasury outcome, not a sale, and putting it in the top line flatters growth and breaks every per-unit ratio built on it.
10. Growth across a currency, quoted without constant currency. −0.6% and −1.8% are the same month; only one describes the business.
11. Text that looks like a currency code. A feed exporting "GBP " with a trailing space matches nothing, and MATCH(C2,...,0) returns #N/A — TRIM on import, and a COUNTIF check that every code in column C exists in the rate table header.
12. Rate tables that are overwritten rather than appended. If the feed replaces the sheet each morning, last month's rates are gone and last month's numbers can never be reproduced.

12) Mini Exercises

Copy the grid into a blank sheet starting at A1 and put the rate table on a sheet called Rates. Each answer is one formula.

1. The rate of the day. In F2:F9, return the rate that applied on each invoice's own date, handling both the currency and the missing Mondays. What does INV-2044 get, and from which row?
2. The month. In G2:G9, the euro amount, rounded where the money is. Confirm the column sums to 132,197.28, then write the SUMPRODUCT version and say what it returns instead.
3. Split it. Produce the four currency subtotals from column D with one formula copied down four cells, and the reporting-currency subtotal for sterling from column G.
4. Catch the disorder. Write the single cell that reports "RATES OUT OF ORDER" if any date in the rate table is not larger than the one above it.
5. Value the open items. For the two unsettled invoices, return the closing-rate euro value at 28 February without typing a date, and the unrealised gain against what was booked.
6. Constant currency. Convert February's four currency subtotals at January's average rates, and state the two growth percentages and the euro amount between them.

Summary

A multi-currency workbook has one job that a single-currency one does not: it has to remember when. An amount and a currency are not enough to produce a euro figure, and the third input — the date the rate came from — is the one that gets left in a cell in the corner where anyone can change it.

Three habits are most of the difference. Look the rate up by date, with MATCH(...,1) or XLOOKUP's -1, and guard the sort order, because that lookup fails silently rather than loudly. Keep the rate on the row once it is found, so a closed month stays closed and every figure can be traced to a published rate on a stated day. And when the euro total moves, say which part of it was the business: 132,353.46 to 131,594.33 is a 0.6% decline, and 132,353.46 to 129,918.06 is the same February with the currency held still — 1.8% down, and 1,676.27 of the gap between them belongs to the exchange rate rather than to anybody's sales team.

The alternative is the version this article started with: a month that was 132,549.03, then 134,880.05, and was 132,197.28 the whole time.