# IF Statement combined with vlookup and formula

#### dc4000

##### New Member
Hello,

This should be straightforward but ive drawn a blank.

We are given;

Sheet1:
Col B: range of currencies in this format. A sample list has been provided but there may be more so the formula has to be dynamic enough to allow for this.
Col C: Value is given but could be any given value
Col D: Requires formula to show the following. Depending on value from Col b then take this rate which can be sourced from Sheet2 and times the value in Col C by this amount. Rates need to be sourced for A1 = 30/09/2018

Sheet1
 Ccy Value Value_Fx'd GBP 1000 GBP 2000 GBP 3000 USD 4000 JPY 5000 DKK 6000 SEK 7000 BRL 8000 GBP 9000 EUR 10000 HKD 11000

<colgroup><col span="2"><col></colgroup><tbody>
</tbody>

<tbody>
</tbody>

Sheet2
 FX_Rates USDJPY USDGBP USDHKD USDEUR USDSEK USDDKK USDBRL 31/08/2018 110.05 0.575 14 1.05 6.5 8.55 2.25 30/09/2018 112.05 0.675 15 1.15 7.5 9.55 3.25

<colgroup><col><col span="7"></colgroup><tbody>
</tbody>

<tbody>
</tbody>

### Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

#### mrshl9898

##### Well-known Member
Like this?

=IF(B2="USD",C2,INDEX(Sheet2!\$B\$2:\$H\$3,MATCH(\$A\$1,Sheet2!\$A\$2:\$A\$3,0),MATCH("USD"&B2,Sheet2!\$B\$1:\$H\$1,0))*C2)

*Assuming Sheet2 has the headers in row 1 and dates in column A

Last edited:

#### dc4000

##### New Member
On sheet1 with the following result as only the column with "USD" gave the required result. However what about the other currencies as these are not populating. The tables below are like-for-like as how the data is displayed. A1 = A1 of the excel worksheet

 A1 Ccy Value Value Fx'd GBP 1000 #N/A =IF(B2="USD",C2,INDEX(Sheet2!\$B\$2:\$H\$4,MATCH(\$B\$2,Sheet2!\$A\$2:\$A\$3,0),MATCH("USD"&B2,Sheet2!\$B\$2:\$H\$2,0))*C2) GBP 1000 #N/A =IF(B2="USD",C2,INDEX(Sheet2!\$B\$2:\$H\$4,MATCH(\$B\$2,Sheet2!\$A\$2:\$A\$3,0),MATCH("USD"&B2,Sheet2!\$B\$2:\$H\$2,0))*C2) GBP 1000 #N/A =IF(B2="USD",C2,INDEX(Sheet2!\$B\$2:\$H\$4,MATCH(\$B\$2,Sheet2!\$A\$2:\$A\$3,0),MATCH("USD"&B2,Sheet2!\$B\$2:\$H\$2,0))*C2) USD 1000 1000 (USING FORMULA BELOW) JPY 1000 #N/A =IF(B2="USD",C2,INDEX(Sheet2!\$B\$2:\$H\$4,MATCH(\$B\$2,Sheet2!\$A\$2:\$A\$3,0),MATCH("USD"&B2,Sheet2!\$B\$2:\$H\$2,0))*C2) DKK 1000 #N/A =IF(B2="USD",C2,INDEX(Sheet2!\$B\$2:\$H\$4,MATCH(\$B\$2,Sheet2!\$A\$2:\$A\$3,0),MATCH("USD"&B2,Sheet2!\$B\$2:\$H\$2,0))*C2) SEK 100 #N/A =IF(B2="USD",C2,INDEX(Sheet2!\$B\$2:\$H\$4,MATCH(\$B\$2,Sheet2!\$A\$2:\$A\$3,0),MATCH("USD"&B2,Sheet2!\$B\$2:\$H\$2,0))*C2) BRL 1000 #N/A =IF(B2="USD",C2,INDEX(Sheet2!\$B\$2:\$H\$4,MATCH(\$B\$2,Sheet2!\$A\$2:\$A\$3,0),MATCH("USD"&B2,Sheet2!\$B\$2:\$H\$2,0))*C2)

<tbody>
</tbody>

Sheet2
 A1 Fx_RATES USDJPY USDGBP USDHKD USDEUR USDSEK USDDKK USDBRL 31/08/2018 30/09/2018 112.05 .675 15 1.15 7.5 9.55 3.25

<tbody>
</tbody>

#### Fluff

##### MrExcel MVP, Moderator
Much the same formula

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;">30/09/2018</td><td style=";">USDJPY</td><td style=";">USDGBP</td><td style=";">USDHKD</td><td style=";">USDEUR</td><td style=";">USDSEK</td><td style=";">USDDKK</td><td style=";">USDBRL</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">31/08/2018</td><td style="text-align: right;;">110.05</td><td style="text-align: right;;">0.575</td><td style="text-align: right;;">14</td><td style="text-align: right;;">1.05</td><td style="text-align: right;;">6.5</td><td style="text-align: right;;">8.55</td><td style="text-align: right;;">2.25</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">30/09/2018</td><td style="text-align: right;;">112.05</td><td style="text-align: right;;">0.675</td><td style="text-align: right;;">15</td><td style="text-align: right;;">1.15</td><td style="text-align: right;;">7.5</td><td style="text-align: right;;">9.55</td><td style="text-align: right;;">3.25</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet2</p><br /><br />

#### mrshl9898

##### Well-known Member
Translated with that extra row:

=IF(B2="USD",C2,INDEX(Sheet2!\$B\$3:\$H\$4,MATCH(\$A\$1,Sheet2!\$A\$3:\$A\$4,0),MATCH("USD"&B2,Sheet2!\$B\$2:\$H\$2,0))*C2)

Or Fluff's:

=IFERROR(INDEX(Sheet2!\$B\$3:\$H\$4,MATCH(\$A\$1,Sheet2!\$A\$3:\$A\$4,0),MATCH("USD"&B2,Sheet2!\$B\$2:\$H\$2,0))*C2,C2)

Replies
3
Views
359