title: Dynamic Currency Conversion in Google Sheets
body: <p>I'm using Google Sheets to organize data from my global royalty statements. Currently I'm querying several tabs (one for each country) to produce a single table with results from all countries. As you can imagine, I don't want 125 Japanese Yen showing up in my charts and graphs as $125 USD (125 Y is equivalent to about $1.09 USD).</p>
  
  <p>Since I receive my royalty statements in their respective currencies, I'd like to apply average conversion rates either during the query operation or after the fact. Since the table is being generated dynamically, the values won't always be the same, so I need some way to apply the conversion by searching the list of currencies on the fly. I've got a separate table on the same tab containing all the average conversion rates for each currency. Here's a sample of how this is set up:</p>
  
  <p><a href="https://i.stack.imgur.com/MkIYd.png" rel="noreferrer"><img src="https://i.stack.imgur.com/MkIYd.png" alt="Google Sheets Royalty Statement"></a></p>
  
  <p>So basically I just don't know how to say, in coding terms, "If this line item comes from the UK, divide the royalty amount by the UK exchange rate. If it comes from Canada, divide by the Canadian rate, etc."</p>
  
  <p>Anyone have any insight as to how I might pull this off (or whether it's possible)? The actual table includes over 500 line items from a dozen different countries, so doing this by hand is something I'd like to avoid.</p>