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

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,488
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
48,242
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,488
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,114,253
Messages
5,546,785
Members
410,757
Latest member
jonni
Top