Currency conversion formula: cross-currency FX table with XLOOKUP

Convert any currency pair from a single 1-column FX table

Maintaining an N-by-N grid for 10 currencies requires 90 exchange rates. Instead, store one column ToUsd = how many USD equal 1 unit of each currency (USD = 1.00, EUR = 1.08, GBP = 1.27, JPY = 0.0065).

Converting Amount from FromCurr to ToCurr multiplies by RateFrom (into USD) and divides by RateTo (into target currency): Amount * XLOOKUP(From, ...) / XLOOKUP(To, ...). When From equals To, the rates cancel to 1.00.

Triangulated cross-rate formula

Bind both lookups with LET and test ISNA before dividing so unrecognized ISO currency codes flag "Missing FX" cleanly.

=LET(fromR, XLOOKUP(D2, A2:A5, B2:B5), toR, XLOOKUP(E2, A2:A5, B2:B5), IF(OR(ISNA(fromR), ISNA(toR)), "Missing FX", ROUND(C2 * fromR / toR, 2)))

Implied cross-rate check (EUR / GBP)

1 EUR = 1.08 / 1.27 = 0.8504 GBP, so 1,000 EUR converts to 850.39 GBP.

=ROUND(XLOOKUP(D2, A2:A5, B2:B5) / XLOOKUP("GBP", A2:A5, B2:B5), 4)

Inputs and interpretation

All rates shown are fictional fixed sample inputs, not current exchange quotes. Use positive numeric rates from the same timestamp/source and one unique currency code per row; zero rates cause division errors and duplicates select the first row. This illustration ignores bid/ask spreads and fees. Two-decimal rounding is chosen for USD/GBP examples, not a universal currency minor-unit rule.

Further reading

• Microsoft Support — XLOOKUP function