If Else Then or IFAnd formula

[Jaimee001]

Hi Team!
Hope everyone is well....

I'm having issues writing a nested if formula, I've tried different types but I cant get it to work
I have a report where column A is a count and Column is the formula
The breaks outs are:
if the count is:
< 100 = 20
>100<350 = 25 (between 100-350)
>350 =1000= 55 (between 351-1000)
>1000 Special Pricing
I'm able to get where the count is less then 100 and it returns 20,
then I'm lost....

I've attached the report and last formula that I tried to make work...

Thank you in advance

Jaimee

 

[NARAYANK991]

Hi Jaimee ,

Try this :

=IF(A2<100,20,IF(AND(A2>=100,A2<=350),25,IF(AND(A2>=351,A2<=1000),55,"Special Pricing")))

Narayan

 

[eibi]

does this work?

=IF(A2<=100,20,IF(A2<=350,25,IF(A2<=1000,55,"special pricing")))

I don't see the need for the AND()

 

[Jaimee001]

Thank you both Naranyank991 and Eibi!!!!!! You're AWESOME!!!

 

[Khalid NGO]

Hi Jaimee,

Or this :
=LOOKUP(A2,{0,101,351,1001},{20,25,55,"special pricing"})

Regards,

 

[shrivallabha]

Range Lookup can also be used if you set up a table to look from.

https://colinlegg.wordpress.com/2012/03/25/binary-searches-with-vlookup/