IF Statement combined with vlookup and formula

[dc4000]

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[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 217"]
<colgroup><col span="2"><col></colgroup><tbody>[TR]
[TD]Ccy[/TD]
[TD]Value[/TD]
[TD]Value_Fx'd[/TD]
[/TR]
[TR]
[TD]GBP[/TD]
[TD="align: right"]1000[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]GBP[/TD]
[TD="align: right"]2000[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]GBP[/TD]
[TD="align: right"]3000[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]USD[/TD]
[TD="align: right"]4000[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]JPY[/TD]
[TD="align: right"]5000[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]DKK[/TD]
[TD="align: right"]6000[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]SEK[/TD]
[TD="align: right"]7000[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]BRL[/TD]
[TD="align: right"]8000[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]GBP[/TD]
[TD="align: right"]9000[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]EUR[/TD]
[TD="align: right"]10000[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]HKD[/TD]
[TD="align: right"]11000[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Sheet2
[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 523"]
<colgroup><col><col span="7"></colgroup><tbody>[TR]
[TD][/TD]
[TD]FX_Rates[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]USDJPY[/TD]
[TD]USDGBP[/TD]
[TD]USDHKD[/TD]
[TD]USDEUR[/TD]
[TD]USDSEK[/TD]
[TD]USDDKK[/TD]
[TD]USDBRL[/TD]
[/TR]
[TR]
[TD="align: right"]31/08/2018[/TD]
[TD="align: right"]110.05[/TD]
[TD="align: right"]0.575[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]1.05[/TD]
[TD="align: right"]6.5[/TD]
[TD="align: right"]8.55[/TD]
[TD="align: right"]2.25[/TD]
[/TR]
[TR]
[TD="align: right"]30/09/2018[/TD]
[TD="align: right"]112.05[/TD]
[TD="align: right"]0.675[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]1.15[/TD]
[TD="align: right"]7.5[/TD]
[TD="align: right"]9.55[/TD]
[TD="align: right"]3.25[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[mrshl9898]

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

 

[dc4000]

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


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A1[/TD]
[TD]Ccy[/TD]
[TD]Value[/TD]
[TD]Value Fx'd[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]GBP[/TD]
[TD]1000[/TD]
[TD]#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)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]GBP[/TD]
[TD]1000[/TD]
[TD]#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)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]GBP[/TD]
[TD]1000[/TD]
[TD]#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)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]USD[/TD]
[TD]1000[/TD]
[TD]1000 (USING FORMULA BELOW)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]JPY[/TD]
[TD]1000[/TD]
[TD]#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)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]DKK[/TD]
[TD]1000[/TD]
[TD]#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)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]SEK[/TD]
[TD]100[/TD]
[TD]#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)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]BRL[/TD]
[TD]1000[/TD]
[TD]#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)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Sheet2
[TABLE="width: 500"]
<tbody>[TR]
[TD]A1[/TD]
[TD]Fx_RATES[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]USDJPY[/TD]
[TD]USDGBP[/TD]
[TD]USDHKD[/TD]
[TD]USDEUR[/TD]
[TD]USDSEK[/TD]
[TD]USDDKK[/TD]
[TD]USDBRL[/TD]
[/TR]
[TR]
[TD]31/08/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]30/09/2018[/TD]
[TD]112.05[/TD]
[TD].675[/TD]
[TD]15[/TD]
[TD]1.15[/TD]
[TD]7.5[/TD]
[TD]9.55[/TD]
[TD]3.25[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

Much the same formula

Excel 2013/2016
	B	C	D
1	Ccy	Value	Value_Fx'd
2	GBP	1000	675
3	GBP	2000	1350
4	GBP	3000	2025
5	USD	4000	4000
6	JPY	5000	560250
7	DKK	6000	57300
8	SEK	7000	52500
9	BRL	8000	26000
10	GBP	9000	6075
11	EUR	10000	11500
12	HKD	11000	165000
Sheet1

Cell Formulas
Range	Formula
D2	=IFERROR(INDEX(Sheet2!B$1:H$3,MATCH(Sheet2!A$1,Sheet2!A$2:A$3,0)+1,MATCH("USD"&B2,Sheet2!B$1:H$1,0))*C2,C2)

Excel 2013/2016
	A	B	C	D	E	F	G	H
1	30/09/2018	USDJPY	USDGBP	USDHKD	USDEUR	USDSEK	USDDKK	USDBRL
2	31/08/2018	110.05	0.575	14	1.05	6.5	8.55	2.25
3	30/09/2018	112.05	0.675	15	1.15	7.5	9.55	3.25
Sheet2

 

[mrshl9898]

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)