Currency Conversion in Single Formula

[DefiantEgg90]

Hello,

I have a table of transactions in different currencies, and am trying to convert and sum all these transactions into USD in a single formula in the row titled Total (USD). I think I'm looking to use a SUMPRODUCT with a lookup as one of the arrays, but not sure how to accomplish this. Any ideas? Sample dataset on the left, conversion table on the right (all conversions are multiplicative):

Transaction	Currency	Amount Local)		Currency	Rate
Transaction 1	ILS	8850​		ILS	0.26857​
Transaction 2	ILS	9676​		EUR	1.093225​
Transaction 3	ILS	19173​		USD	1​
Transaction 4	EUR	4892​
Transaction 5	EUR	4755​
Transaction 6	EUR	2946​
Transaction 7	USD	500​
Transaction 8	USD	200​
Transaction 9	USD	100​
Total (USD)

 

[Joe4]

Why not just use a VLOOKUP formula, where you look up the corresponding conversion factor from your table and multiply by that?
See: VLOOKUP Function

 

[6StringJazzer]

To wit:

$scratch.xlsm
	A	B	C	D	E	F	G
1	Transaction	Currency	Amount (Local)	Amount USD		Currency	Rate
2	Transaction 1	ILS	8850	2376.8445		ILS	0.26857
3	Transaction 2	ILS	9676	2598.68332		EUR	1.09323
4	Transaction 3	ILS	19173	5149.29261		USD	1
5	Transaction 4	EUR	4892	5348.0567
6	Transaction 5	EUR	4755	5198.284875
7	Transaction 6	EUR	2946	3220.64085
8	Transaction 7	USD	500	500
9	Transaction 8	USD	200	200
10	Transaction 9	USD	100	100
11	Total (USD)
Sheet5

Cell Formulas
Range	Formula
D2:D10	D2	=$C2*VLOOKUP($B2,$F:$G,2,FALSE)