nested if with multiple criteria,

[paul w]

Hi all,
Not an excel expert, so I was wondering if there is a more efficient way of doing this formula, it checks for a product type then a box type and a box number, then returns the correct price for that scenario,
the formula works, but I'm sure there must be a better way to do this without using 13 nested ifs,
also if I want to change the prices for product 2 for example in the pricing sheet, or add more products, it will be a night mare and another 9 nested ifs, thinking a lookup type formula but I don't think you can use lookup for multiple criteria
attached a dropbox link for a test sheet,
https://www.dropbox.com/s/dkd33iszp4liv3b/Test sheet - Copy.xlsx?dl=0

This is the formula, like I say it works (I think it slows down when trying to close the workbook) but will be a nightmare to edit or add too.

=IF(AND(D2>=2,D2<=5,E2>=4,E2<=10,OR(C2="product 1",C2="product 2",C2="product 3")),'2018 pricing'!$C$4,
IF(AND(D2>=2,D2<=5,E2>=11,E2<=21,OR(C2="product 1",C2="product 2",C2="product 3")),'2018 pricing'!$D$4,
IF(AND(D2>=2,D2<=5,E2>=22,OR(C2="product 1",C2="product 2",C2="product 3")),'2018 pricing'!$E$4,
IF(AND(D2>=6,D2<=10,E2>=4,E2<=10,OR(C2="product 1",C2="product 2",C2="product 3")),'2018 pricing'!$C$5
IF(AND(D2>=6,D2<=10,E2>=11,E2<=21,OR(C2="product 1",C2="product 2",C2="product 3")),'2018 pricing'!$D$5,
IF(AND(D2>=6,D2<=10,E2>=22,OR(C2="product 1",C2="product 2",C2="product 3")),'2018 pricing'!$E$5,
IF(AND(D2>=11,E2>=4,E2<=10,OR(C2="product 1",C2="product 2",C2="product 3")),'2018 pricing'!$C$6,
IF(AND(D2>=11,E2>=11,E2<=21,OR(C2="product 1",C2="product 2",C2="product 3")),'2018 pricing'!$D$6,
IF(AND(D2>=11,E2>=22,OR(C2="product 1",C2="product 2",C2="product 3")),'2018 pricing'!$E$6,
IF(AND(D2>=2,E2>=4,E2<=10,OR(C2="product 4",C2="product 5")),'2018 pricing'!$C$13,
IF(AND(D2>=2,E2>=11,E2<=21,OR(C2="product 4",C2="product 5")),'2018 pricing'!$D$13,
IF(AND(D2>=2,E2>=22,OR(C2="product 4",C2="product 5")),'2018 pricing'!$E$13,
IF(OR(C2="product 6",C2="product 7",C2="product 8"),'2018 pricing'!$C$15,"Missing Data")))))))))))))

many thanks

Paul

 

[paul w]

Re:[Solved] nested if with multiple criteria,

I couldn't resist trying this, and it works fantastic, many thanks for you help,
its also pretty much future proof as well,
I've also learnt a lot about the [index] [match] formula, for that I cant thank you enough.

many thanks,

paul

 

[Fluff]

Re: [Solved] nested if with multiple criteria,

Glad to help & thanks for the feedback