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

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>

 

[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

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]

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)