IF Statement combined with vlookup and formula

dc4000

New Member
Joined
Jan 19, 2018
Messages
8
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
CcyValueValue_Fx'd
GBP1000
GBP2000
GBP3000
USD4000
JPY5000
DKK6000
SEK7000
BRL8000
GBP9000
EUR10000
HKD11000

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

<tbody>
</tbody>

Sheet2
FX_Rates
USDJPYUSDGBPUSDHKDUSDEURUSDSEKUSDDKKUSDBRL
31/08/2018110.050.575141.056.58.552.25
30/09/2018112.050.675151.157.59.553.25

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

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,416
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
Joined
Jan 19, 2018
Messages
8
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


A1CcyValueValue Fx'd
GBP1000#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)
GBP1000#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)
GBP1000#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)
USD10001000 (USING FORMULA BELOW)
JPY1000#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)
DKK1000#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)
SEK100#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)
BRL1000#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
A1Fx_RATES
USDJPYUSDGBPUSDHKDUSDEURUSDSEKUSDDKKUSDBRL
31/08/2018
30/09/2018112.05.675151.157.59.553.25

<tbody>
</tbody>
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,416
Office Version
  1. 365
Platform
  1. Windows
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 /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Ccy</td><td style=";">Value</td><td style=";">Value_Fx'd</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">GBP</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">675</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">GBP</td><td style="text-align: right;;">2000</td><td style="text-align: right;;">1350</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">GBP</td><td style="text-align: right;;">3000</td><td style="text-align: right;;">2025</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">USD</td><td style="text-align: right;;">4000</td><td style="text-align: right;;">4000</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">JPY</td><td style="text-align: right;;">5000</td><td style="text-align: right;;">560250</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">DKK</td><td style="text-align: right;;">6000</td><td style="text-align: right;;">57300</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">SEK</td><td style="text-align: right;;">7000</td><td style="text-align: right;;">52500</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">BRL</td><td style="text-align: right;;">8000</td><td style="text-align: right;;">26000</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">GBP</td><td style="text-align: right;;">9000</td><td style="text-align: right;;">6075</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">EUR</td><td style="text-align: right;;">10000</td><td style="text-align: right;;">11500</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">HKD</td><td style="text-align: right;;">11000</td><td style="text-align: right;;">165000</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)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D2</th><td style="text-align:left">=IFERROR(<font color="Blue">INDEX(<font color="Red">Sheet2!B$1:H$3,MATCH(<font color="Green">Sheet2!A$1,Sheet2!A$2:A$3,0</font>)+1,MATCH(<font color="Green">"USD"&B2,Sheet2!B$1:H$1,0</font>)</font>)*C2,C2</font>)</td></tr></tbody></table></td></tr></table><br />


<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
Joined
Feb 6, 2012
Messages
1,416
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,109,146
Messages
5,527,076
Members
409,743
Latest member
sukuto20

This Week's Hot Topics

Top