EUR GBP USD
Jan-09 1.1205 1.0000 1.4500
Feb-09 1.1261 1.0000 1.4573
Mar-09 1.1317 1.0000 1.4645
Apr-09 1.1374 1.0000 1.4719
May-09 1.1431 1.0000 1.4792
Jun-09 1.1488 1.0000 1.4866
Jul-09 1.1545 1.0000 1.4940
Aug-09 1.1603 1.0000 1.5015
Sep-09 1.1661 1.0000 1.5090
Oct-09 1.1719 1.0000 1.5166
Nov-09 1.1778 1.0000 1.5242
Dec-09 1.1837 1.0000 1.5318

I need the formula to return a correct value based on month and currency.

I have came up with this

=IF(B28=B$1,VLOOKUP(A28,A$2:E$25,2,0),IF(B28=C$1,V LOOKUP(A28,A$2:E$25,3,0),IF(B28=D$1,VLOOKUP(A28,A$ 2:E$25,4,0),0)))

Is it possible to make it simpler, especially when i need to add more
currencies and months to it?

Thanks

Edwin

Hi,

=index($B$2:$E$25,Match(B28,$B$1:$D$1,0),Match(A28 ,$A$2:$A$25,0))

"edwin" wrote:

EUR GBP USD
Jan-09 1.1205 1.0000 1.4500
Feb-09 1.1261 1.0000 1.4573
Mar-09 1.1317 1.0000 1.4645
Apr-09 1.1374 1.0000 1.4719
May-09 1.1431 1.0000 1.4792
Jun-09 1.1488 1.0000 1.4866
Jul-09 1.1545 1.0000 1.4940
Aug-09 1.1603 1.0000 1.5015
Sep-09 1.1661 1.0000 1.5090
Oct-09 1.1719 1.0000 1.5166
Nov-09 1.1778 1.0000 1.5242
Dec-09 1.1837 1.0000 1.5318

I need the formula to return a correct value based on month and currency.

I have came up with this

=IF(B28=B$1,VLOOKUP(A28,A$2:E$25,2,0),IF(B28=C$1,V LOOKUP(A28,A$2:E$25,3,0),IF(B28=D$1,VLOOKUP(A28,A$ 2:E$25,4,0),0)))

Is it possible to make it simpler, especially when i need to add more
currencies and months to it?

Thanks

Edwin

Using VLOOKUP()

=VLOOKUP(A28,A2:D25,MATCH(B28,A1:D1,0),0)

--
Jacob (MVP - Excel)


"edwin" wrote:

EUR GBP USD
Jan-09 1.1205 1.0000 1.4500
Feb-09 1.1261 1.0000 1.4573
Mar-09 1.1317 1.0000 1.4645
Apr-09 1.1374 1.0000 1.4719
May-09 1.1431 1.0000 1.4792
Jun-09 1.1488 1.0000 1.4866
Jul-09 1.1545 1.0000 1.4940
Aug-09 1.1603 1.0000 1.5015
Sep-09 1.1661 1.0000 1.5090
Oct-09 1.1719 1.0000 1.5166
Nov-09 1.1778 1.0000 1.5242
Dec-09 1.1837 1.0000 1.5318

I need the formula to return a correct value based on month and currency.

I have came up with this

=IF(B28=B$1,VLOOKUP(A28,A$2:E$25,2,0),IF(B28=C$1,V LOOKUP(A28,A$2:E$25,3,0),IF(B28=D$1,VLOOKUP(A28,A$ 2:E$25,4,0),0)))

Is it possible to make it simpler, especially when i need to add more
currencies and months to it?

Thanks

Edwin