drop down dependent hlookup with currency conversion?

[RustyDee]

Hi folks,

I hope you can help, I'm trying to run a PO/budget sheet with 4 currencies in it.
I'd like to have a column at the end which calculates my monthly outgoings in GBP, using a fixed exchange rate.

I'd like the function to see which column (B/C/D/E) has a figure in it, convert it to GBP (with the appropriate exchange rate that I can store somewhere on the sheet if necessary) and then place that figure in column G.

I put column F in as I thought it might help the formula do the calculation but am happy to remove it if not needed.

I did try the following but they didn't work - maybe I was using the wrong formulas

=INDEX(B2:E2,LOOKUP("F2",B1:E1,FALSE))
and also
=HLOOKUP(F2,B2:E2,2,FALSE)


[TABLE="width: 500"]
<tbody>[TR]
[TD]PO number[/TD]
[TD]£[/TD]
[TD]AU$[/TD]
[TD]US$[/TD]
[TD]€[/TD]
[TD]Currency[/TD]
[TD]Total outgoings £GBP[/TD]
[/TR]
[TR]
[TD]PO Jan01[/TD]
[TD]500.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]GBP[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PO Jan02[/TD]
[TD][/TD]
[TD][/TD]
[TD]350[/TD]
[TD][/TD]
[TD]US$[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PO Jan03[/TD]
[TD][/TD]
[TD]1000[/TD]
[TD][/TD]
[TD][/TD]
[TD]AU$[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PO Jan04[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]400[/TD]
[TD]€[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[oldbrewer]

put usd in Y1 and AUD in Z1 and eurosymbol in AA1
and put the updateable conversion factors in Y2 and Z2 and AA2

then in G3 (there is 350 usd in D3) sum(b3:e3)*offset($x$1,match(f3,1,$y$1:$aa$1))

this will find the correct conversion factor to convert the 350 usd

 

[RustyDee]

Thanks Oldbrewer - I don't have anything in cell X1 though, so I'm getting an error saying I've entered too few arguments for the function - what should go there?

Thanks again!

put usd in Y1 and AUD in Z1 and eurosymbol in AA1
and put the updateable conversion factors in Y2 and Z2 and AA2

then in G3 (there is 350 usd in D3) sum(b3:e3)*offset($x$1,match(f3,1,$y$1:$aa$1))

this will find the correct conversion factor to convert the 350 usd

 

[oldbrewer]

X1 is the starting point for the offset - it can be empty

offset works by defining the start point, then going down, then going across

I erred - it should be offset($x$1,1, match(f3,$y$1:$aa$1,0)

(the 1 somehow got into the match - sorry)

(the last 0 in the match requires an exact match)

 

[RustyDee]

Awesome - it's working! Thanks so much!