5. Enter Price in cell B1. The table will update as shown in Figure 5.
The Accounting number format applies automatically to the Price (see Figure 3). This format places the $ sign in the left of the cell.
I prefer to use the Currency number format, which places the $ sign next to the number as per Figure 5. Although only two decimal places are displayed, the exchange rate has four decimal places.
To make this table easier to look up, we can add a code for the currency that is being converted to AUD. This assumes all the codes we list in this table will be converting to AUD.
6. Enter Code in cell C1.
7. Enter the following formula in cell C2:
=LEFT([@[Exchange Rates]].[Ticker symbol],3)
8. The ticker symbol field for the NZD/AUD conversion is NZDAUD. The above formula extracts the first three characters from the ticker symbol field.
9. Since the first table has multiple currencies, we can now standardise the values and convert all the Prices to AUD using our second table.
10. I will name the Exchange Rate table tblRates. You can do this in the Table Design tab (far left) when the table is selected. I use a prefix of tbl for formatted tables, so they list together when I type tbl in formulas. It also tells me the name is for a formatted table rather than a range name.
11. In our first table, enter Exchange Rate in cell E1. In cell E2, to extract the exchange rate from the other table, enter the following formula:
=IF([@Currency]="AUD",1,XLOOKUP([@Currency],tblRates[Code],tblRates[Price],0))
12. The IF functionreturns 1 if the currency is AUD. The XLOOKUP functionwill extract the rate from the exchange rate table if it finds the currency code. It will return zero if the Currency code cannot be found. If a new currency code appears in the first table, you can enter its exchange rate code in the exchange rate table to enable conversions.
13. We can add a further column to the first table. Enter Price in AUD in cell F1. In cell F2, enter the following formula:
=[@[Exchange Rate]]*[@Price]
14. If you use the mouse to select cells B2 and E2, Excel automatically enters the references with the square brackets. These are called structured references and are part of the formatted table functionality. Figure 6 shows the final table.
