which converts the amount in column B from US Dollars (USD) to the currency indicated in column D, where the named range “xtable” refers to G5:H10. The core of this formula is the VLOOKUP function, configured like this: Here, the lookup value comes from column D, table array is the named range “xtable”, column index is 2, and match type is set to exact match. In this configuration, VLOOKUP finds the currency in the table, and retrieves the conversion rate from column H. If the currency code

Nested IF equivalent

With a formula based on nested IFs, the solution looks like this: Line breaks added for better readability. Note: VLOOKUP is generally a better solution, since values are easy to see and edit at any time, the formula is easier to read, and exchange rate values are not stored redundantly in many different formulas.

INDEX and MATCH equivalent

With the INDEX and MATCH funcions, the solution looks like this:

Dave Bruns

Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.