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

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.

``````=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}))
• Was looking over another attempt I made earlier, which almost worked. Is there a reason as to why the F2 cell would be ignored in the below formula for both Sale and Import validations? Google Sheets correctly calculates everything but ignores the F2 cell. `=IFS(A2="Import",C2*B2+F2,A2="Sale",B2*C2-F2)*IFS(G2="GBP",Data!B\$31,G2="EUR",H2,G2="USD",H2)` Cheers! Jun 9 '20 at 17:24