SOLVED

New Contributor

# If/or/and formula

I am looking for a formula that will help me to choose my commission on sales based as follows, will be very grateful for your urgent help

 \$75 Up to \$1000 \$150 \$1001 - \$ 5000 \$225 \$5001 and above
6 Replies

# Re: If/or/and formula

=VLOOKUP(D1,CHOOSE({1,2},\$B\$1:\$B\$3,\$A\$1:\$A\$3),2,1)

Is this what you are looking for?

# Re: If/or/and formula

Thank you for the speedy reply but what I need is a self-contained formula that can return the commission value based on the variable. I started doing the formula but got stuck halfway through. Here is my attempt at it, I am sure you will be able to complete it. The formula is in B6. Please help

=IF(OR(AND(\$D\$1>0,\$D\$1<1001), AND(\$D\$1>1000,\$D\$1<5001)),75,150)

best response confirmed by baldevdhot (New Contributor)
Solution

# Re: If/or/and formula

=IF(AND(\$D\$1>0,\$D\$1<1001),75,

IF(AND(\$D\$1>1000,\$D\$1<5001),150,

IF(\$D\$1>=5001,225)))

Above nested IF formula works in my spreadsheet.