Currency Conversion in Excel With a Rate Table and XLOOKUP

Currency conversion in Excel comes down to one step: multiply each amount by the right exchange rate. The reliable way is to keep the rates in a small table and let XLOOKUP pick the rate for each row's currency code. This guide is for anyone who handles invoices, expenses or sales in more than one currency, and it builds the whole setup on one small example.

How to set up a rate table#

Put every rate in one place, with a label that says which way the rate goes. Below, invoices are in A1:D6 and the rate table is in F1:G6. Every rate is written as US dollars per 1 unit of the other currency.

These rates are example values for practice, not real current rates. Exchange rates change all the time. In a real file, copy rates from the source your company uses, such as your bank or finance team, and write the date next to the table.

Use the three-letter ISO codes (USD, EUR, GBP) rather than names or symbols. Codes are short, unique and easy to match.

The currency conversion formula with XLOOKUP#

XLOOKUP finds the rate for each currency code, and the formula multiplies the amount by it. In D2, type:

=B2*XLOOKUP(C2,$F$2:$F$6,$G$2:$G$6)

• C2 is the code to look for (EUR).
• $F$2:$F$6 is where to look for it.
• $G$2:$G$6 is the column of rates to bring back.

The result is 1,296 (1,200 × 1.08). Fill the formula down to D6 (Windows: Ctrl+D; Mac: ⌘D):

The total of the USD column is 5,960.50. XLOOKUP needs Excel 2021 or Microsoft 365. In older versions, use =B2*VLOOKUP(C2,$F$2:$G$6,2,FALSE), which gives the same results. The XLOOKUP function guide and the article on how to use XLOOKUP cover the extra arguments.

Why the $ signs matter#

The $ signs keep the rate table fixed when you fill the formula down. Without them, D3 would look in F3:F7, D4 in F4:F8, and so on. The table would slide down, and the lower rows would miss rates or return #N/A.

C2 has no $, so it moves to C3, C4 and C5 as you fill. That is what you want: each row looks up its own currency. To add the $ signs, click on the reference in the formula bar and press F4 on Windows or ⌘T on a Mac. The guide to absolute references and the dollar sign explains all three kinds of reference.

Multiply or divide? Check the rate direction#

An exchange rate can be written two ways, and using the wrong one gives a badly wrong answer. With the example rate of 1.08 US dollars per euro:

• USD per 1 EUR = 1.08. To turn euros into dollars, multiply: 1,200 × 1.08 = 1,296.
• EUR per 1 USD = 0.9259 (1 ÷ 1.08). To turn euros into dollars, divide: 1,200 ÷ 0.9259 ≈ 1,296.

Both give the same dollars, but only if you use the matching operation. Label the rate column clearly, such as "USD per 1 unit", so nobody has to guess.

A quick sense check catches most mistakes. Here, a euro is worth a bit more than a dollar, so the dollar amount should be a bit bigger than the euro amount. If the result is smaller, you've used the rate the wrong way round.

How to round converted amounts#

Wrap the formula in ROUND so each amount stops at the cent:

=ROUND(B2*XLOOKUP(C2,$F$2:$F$6,$G$2:$G$6),2)

Rates often have four or more decimal places, so the raw result can be 1,206.4999 or 1,752.0012. Formatting only hides the extra digits. The hidden digits still go into totals, which can then differ from the visible amounts by a cent. ROUND changes the stored value, so the total matches what people see. The ROUND function guide and the guide to Excel's rounding functions explain when to use ROUNDUP or ROUNDDOWN instead.

To show a symbol, select the column and choose Home > Number Format > Currency, or pick the symbol in Format Cells. The article on formatting numbers as currency and percentages shows how.

How to convert between two other currencies#

With every rate stored against one base currency, you can convert between any two currencies in the table. Go through the base: multiply by the "from" rate, then divide by the "to" rate. To turn the 1,200 EUR into British pounds:

=ROUND(B2*XLOOKUP("EUR",$F$2:$F$6,$G$2:$G$6)/XLOOKUP("GBP",$F$2:$F$6,$G$2:$G$6),2)

That is 1,200 × 1.08 ÷ 1.27 = 1,020.47 pounds. In a real sheet, put the target code in a cell, such as I1, and refer to it instead of typing "GBP".

How to use a different rate for each date#

If rates change daily and each invoice should use the rate from its own date, add a date column to the rate table, sorted oldest to newest. Then ask XLOOKUP for the exact date or the closest earlier one:

=XLOOKUP(A2,$J$2:$J$40,$K$2:$K$40,"No rate",-1)

The -1 means "exact match, or the next smaller date", so a Sunday invoice takes Friday's rate. For several currencies, give each currency its own rate column in the dated table. The guide to looking up values by date walks through this pattern.

Microsoft 365 can also fetch rates with the Currencies data type on the Data tab. If you use it, check where the rates come from and when they were last updated before you rely on them.

Practise it#

Try the exercise on this page: one XLOOKUP inside ROUND, with a locked rate table. Then practise XLOOKUP in a live spreadsheet, where every formula is checked as you type, and practise absolute references so your rate table stays put when you fill down.