Excel IF AND formula issue!

July 19, 2011 at 04:08:25
Specs: Windows Vista
I have a column with the numbers either 1, 2 or 3 relating to different currency - 1 stands for £, 2 stands for € and 3 stands for $. I then have 3 columns with the current exchange rate for each currency. I need a further column to have the correct formula to select the correct currency figure by reading what number is in the first column. So, if the first column has the number 2 in it, I need a formula that will give me the figure I have for the Euro exchange rate - but if I change the figure from 2 to 3, it needs to then automatically give me the figure for the $ currency - and the same if I change the figure to 1 it needs to give me the sterling currency figure.


See More: Excel IF AND formula issue!

Report •

July 19, 2011 at 04:53:00
You didn't say what you wanted to do with the exchange rate, so this formula will simply return it.

Assuming your 1, 2, 3 are in Column A and your exchange rates are in Column B:D, try this:


Note: This formula will return D1 if A1 is anything except 1 or 2.

If it were me, I'd use a Data Validation Drop Down in A1 to ensure that only a 1, 2 or 3 were entered.

You could also use something like this to ensure a 1,2 or 3 is entered:

=IF(AND(A1<>1,A1<>2,A1<>3),"Enter a 1, 2 or 3 in A1",IF(A1=1,B1,IF(A1=2,C1,D1)))

If you need to actually do something with the exchange rate, like multiply a value from another cell with it, something like this should work.


Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •
Related Solutions

Ask Question