How to convert currencies with an exchange rate table in Excel
Formula (Excel and Google Sheets)
=ROUND(B2*XLOOKUP(C2,$F$2:$F$6,$G$2:$G$6),2)Multiply each amount by the rate looked up for its currency: =ROUND(B2*XLOOKUP(C2,$F$2:$F$6,$G$2:$G$6),2).
- Result on the example
- one result per row in
D2, filled down toD7 - Works in
- Excel 2021, 2024 and Microsoft 365
- Google Sheets
- Same formula
See it work
The formula is already in D2, filled down to D7. Change any input and the result updates, or edit the formula to experiment.
How it works
XLOOKUP finds the expense's currency code in the rate table and returns how many dollars one unit is worth, 1.0872 for EUR. Multiplying by the amount converts it, and ROUND keeps the result to cents so the converted lines add up the way they display. USD is in the table with a rate of 1, so dollar expenses pass through unchanged.
=ROUND(B2*XLOOKUP(C2,$F$2:$F$6,$G$2:$G$6),2)Result448.47
B2412.50The value in B2.
C2"EUR"The value in C2.
$F$2:$F$65 rows × 1 col: "EUR", "GBP", "CHF", "JPY", …A range: 5 cells in one column.
$G$2:$G$65 rows × 1 col: 1.0872, 1.2718, 1.1281, 0.006712, …A range: 5 cells in one column.
XLOOKUP(C2,$F$2:$F$6,$G$2:$G$6)1.0872XLOOKUP: Searches one range and returns the matching item from another.- lookup_value
C2"EUR" - lookup_array
$F$2:$F$65 rows × 1 col: "EUR", "GBP", "CHF", "JPY", … - return_array
$G$2:$G$65 rows × 1 col: 1.0872, 1.2718, 1.1281, 0.006712, …
- lookup_value
B2*XLOOKUP(C2,$F$2:$F$6,$G$2:$G$6)448.47The * operator multiplies.
ROUND(B2*XLOOKUP(C2,$F$2:$F$6,$G$2:$G$6),2)448.47ROUND: Rounds to a number of digits; negative digits round left of the decimal.- number
B2*XLOOKUP(C2,$F$2:$F$6,$G$2:$G$6)448.47 - num_digits
2
- number
In Google Sheets
The same formula works in Google Sheets: =ROUND(B2*XLOOKUP(C2,$F$2:$F$6,$G$2:$G$6),2). We ran it in Google Sheets mode on the same data and it returns the same results.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =ROUND(B2*VLOOKUP(C2,$F$2:$G$6,2,FALSE),2) | $448.47, $108.30, $107.09… | The VLOOKUP version, for every Excel version. |
Common mistakes
- Check which way the rate is quoted. If the table says how many euros one dollar buys, divide instead of multiplying.
- A currency missing from the table returns #N/A for that line, which is better than silently using a wrong rate.
- Rates change daily. To use the rate on each expense's date, look it up from a dated rate table.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Convert currencies with an exchange rate table
D2, convert the amount in B2 to US dollars using the rate for its currency in F2:G6, rounded to cents. Fill down to D7.Questions
How do I get today's rates into the table?
In Google Sheets, =GOOGLEFINANCE("CURRENCY:EURUSD") returns the current rate. In Excel 365, the Currencies data type does the same.
How do I convert to a currency other than dollars?
Divide by the target currency's rate: =B2*XLOOKUP(C2,F2:F6,G2:G6)/XLOOKUP("EUR",F2:F6,G2:G6) converts to euros.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.