Format cell with currency informed in other cell using a single formula

[Tahitipit]

HI,

I cell B1 I have a drop down list to select the currencies that are written in the ISO currency code (EUR, USD,....)
cell C1 I have the qty
Cell D1 the unit value
Cell E1 is C1*D1

I am looking for a formula to format the cell E1 as currency using the currency informed in B1

I have found solutions with conditional formating, with concatenation and also with macro , but cannot figure out a formula that would use B1 as argument to set the cell format as currency and using the currency in B1

Any idea ?

Thanks

 

[AlphaFrog]

Have a lookup table (F1:G3 in this example) that you can define the currency formats for each ISO code. Then use a VLOOKUP within a TEXT function to format the value.

F	G
US	$#,##0.00
EU	#,##0.00 €
UK	£#,##0.00

<tbody>
</tbody>

=TEXT(C1*D1,VLOOKUP(B1,F1:G3,2,0))

 

[Sam_D_Ben]

Another way is,

Excel 2013/2016
	A	B	C	D	E	F
1	C-List	Quantity	Unit Value	Net		Validation
2	USD	2	12.5	25.00$		USD
3						EUR
4						GBP
5						YEN
Sheet11

Cell Formulas
Range	Formula
D2	=TEXT(SUM(B2*C2),"#,##0.00"&IF(A2="USD",CHAR(36),IF(A2="EUR",CHAR(128),IF(A2="GBP",CHAR(163),IF(A2="YEN",CHAR(165))))))