Nesting IF, AND, and OR in Google Sheets or another way?

Question

So I've been searching online for what seems like forever to try and locate a way to achieve this in Google Sheets. The aim here is to choose a currency and sale type using data validation in two cells and run a function depending on the values of both those cells in a third cell.

A1 is data validation to select a currency (GBP, USD, EUR, etc.). B1 is data validation to select a transaction (Sale, Refund). C1 outputs the transacted value.

D1 holds price. E1 holds fee cost. F1-F3 hold currency conversions.

Here's how it needs to work: If B1 is a sale, a function needs to run to calculate the total transaction, adding a fee. If B1 is a refund, the same function needs to run, but to subtract a fee. This all then takes into account the conversion rate for the currency selected. So I essentially require Sheets to check the currency, check whether it's a sale or refund, then run the function.

Toying with IF this seems to be far too complex with two functions per currency required, depending on whether it's a sale or refund. It's at a point where my brain is turning to mush and I ended up with the following, which of course doesn't work.

=lookup(A1,
       {"GBP","EUR","USD"},
       {if(B1="Sale",D1+E1*F1,if(B1="Refund",D1-E1*F1)), 
        if(B1="Sale",D1+E1*F2,if(B1="Refund",D1-E1*F2)), 
        if(B1="Sale",D1+E1*F3,if(B1="Refund",D1-E1*F3))}
)

Praying that someone is able to slap me down for my stupidity and point me in the correct direction.

Answer 1

You need the lookup only for the exchange rate, and the difference between adding and subtracting a term can also be accomplished by multiplying with 1 or -1 and always adding it. If you're certain B1 always contains either "Sale" or "Refund", you can go for:

=D1+(IF(B1="Sale",1,-1))*E1*LOOKUP(A1,{"GBP","EUR","USD"},{F1,F2,F3})

but it looks like your current formula produces FALSE if it isn't, and then you'll need something like this:

=IF(OR(B1="Sale",B1="Refund"),D1+(IF(B1="Sale",1,-1))*E1*LOOKUP(A1,{"GBP","EUR","USD"},{F1,F2,F3}))