Convert Currency with a Rate Table

All versionsVLOOKUP

Keep your exchange rates in one small table and convert any amount with a lookup. Update the rate in the table and every conversion across the sheet refreshes — no formula edits.

Quick formula: with a rate table in E2:F6 (currency, rate-to-USD) and a EUR amount in B2:

=B2 * VLOOKUP("EUR", $E$2:$F$6, 2, FALSE)

Multiply the foreign amount by its rate-to-USD to get dollars. Divide instead to go from USD to the foreign currency.

Functions used (tap for the full reference guide):

VLOOKUP XLOOKUP

The example

€250 converted to USD at 1.08.

	A	B
1	Currency	→ USD
2	EUR	1.08
3	GBP	1.27
4	JPY	0.0067
5	€250 → USD	$270.00

The formula

Convert using the matched rate:

=B2 * VLOOKUP(C2, $E$2:$F$6, 2, FALSE) // €250 × 1.08 = $270.00

How it works

A rate table keeps conversions consistent:

Store each currency’s rate to USD in a table — how many dollars one unit is worth.
VLOOKUP(currency, table, 2, FALSE) pulls the right rate; multiply by the amount for USD.
To convert USD → foreign, divide instead: =usdAmount / rate.
Cross-rates (EUR → GBP) go via USD: =eur * eurRate / gbpRate.

Live rates: Excel 365 with the Currencies data type can pull market rates automatically, and STOCKHISTORY can fetch historical FX. For most sheets a manually maintained rate table is simpler and keeps results stable for a given report.

Try it: interactive demo

Live demo

Pick a currency and amount → USD.

From

Amount

In USD

Variations

USD → foreign

Divide by the rate:

=usdAmount / VLOOKUP(C2, $E$2:$F$6, 2, FALSE)

Cross rate (EUR→GBP)

Go through USD:

=eur * eurRate / gbpRate

XLOOKUP (365)

With a not-found message:

=B2 * XLOOKUP(C2, curr, rates, "no rate")

Pitfalls & errors

Exact match for codes. Use FALSE — currency codes need an exact lookup, not approximate.

Know your rate direction. Decide whether the table stores units-per-USD or USD-per-unit, and multiply or divide accordingly — getting it backwards is the classic FX error.

Rates change. A static table is a snapshot. Note the rate date, and refresh before relying on conversions for anything material.

Practice workbook

📊

Download the free Convert Currency with a Rate Table practice workbook

A currency converter with a rate table, the reverse, cross-rate, and XLOOKUP variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I convert currency in Excel?

Keep a rate table and use =amount * VLOOKUP(currency, table, 2, FALSE), where the table stores each currency's rate to USD. To go from USD to a foreign currency, divide by the rate instead.

How do I convert between two non-USD currencies?

Go through USD: =amount * fromRate / toRate. For example EUR→GBP is =eur * eurRateToUSD / gbpRateToUSD.

Can Excel pull live exchange rates?

Excel 365's Currencies data type can fetch market rates, and STOCKHISTORY can pull historical FX. A manual rate table is simpler and keeps a report's numbers stable.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Sales tax by region · VLOOKUP basics · Convert units

Function references: VLOOKUP · XLOOKUP