# How to convert currencies with an exchange rate table in Excel

Multiply each amount by the rate looked up for its currency: `=ROUND(B2*XLOOKUP(C2,$F$2:$F$6,$G$2:$G$6),2)`.

**Excel:** `=ROUND(B2*XLOOKUP(C2,$F$2:$F$6,$G$2:$G$6),2)` (Excel 2021, 2024 and Microsoft 365)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F | G |
| --- | --- | --- | --- | --- | --- | --- |
| Expense | Amount | Currency | Amount (USD) |  | Currency | USD per unit |
| Hotel Lisbon | 412.5 | EUR |  |  | EUR | 1.0872 |
| Train Zurich | 96 | CHF |  |  | GBP | 1.2718 |
| Dinner London | 84.2 | GBP |  |  | CHF | 1.1281 |
| Taxi NYC | 38.75 | USD |  |  | JPY | 0.006712 |
| Hotel Tokyo | 31800 | JPY |  |  | USD | 1 |
| Museum Paris | 22 | EUR |  |  |  |  |

Formula in D2, filled to D7: `=ROUND(B2*XLOOKUP(C2,$F$2:$F$6,$G$2:$G$6),2)`

Result: $448.47; $108.30; $107.09; $38.75; $213.44; $23.92

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

## Other ways

- `=ROUND(B2*VLOOKUP(C2,$F$2:$G$6,2,FALSE),2)`: 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.

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

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/convert-currency-with-rate-table