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

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,490
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,448
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,490
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,528
Messages
5,548,570
Members
410,851
Latest member
glowe2020
Top