looking in numerous cells but only posting 1 value (if that makes sense) (1st time user)

[medek]

hi 1st time user here

i have spreadsheet for all the products i purchase. i buy in 3 different currencies $, Euro & £. i have 3 separate columns (f, g & H) for the 3 different currencies and only 1 of them is supposed to be populated with the relevant cost price e.g. cell F5 will say $2.50 or cell G5 Euro5.00 etc.

at the top of each of these columns i have the exchange rate for each of the currencies (f2, g2 & h2)

In column "I" i need it to say the exchange rate for that product however i need a formula to select the correct exchange rate based on which field in the buying price columns is populated. would also be helpful if an error message would generate if 2 buying fields were populated.

hope i've been clear enough and hoping someone can help me.

 

[tony0682]

Think this would just need a simple nested-if formula.

=IF(OR(AND(F5>0,G5>0),AND(F5>0,H5>0),AND(G5>0,H5>0))=TRUE,"Two amounts were entered",IF(F5>0,$F$2,IF(G5>0,$G$2,IF(H5>0,$H$2,"No Amount Entered"))))

 

[Aaron Blood]

I would do it like this:
=IF(COUNT(F5:H5)>1, "oops!", SUMPRODUCT(--(F5:H5<>0),F$2:H$2))

 

[medek]

Thanks so much. Both work. Top Stuff