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

 

[Fluff]

If you're willing to change your lookup table to

Excel 2013/2016
	A	B	C	D	E
1	Product	Box Type	2018
2			Number of Boxes
3			4	11	22
4	product 1	2	£800.00	£960.00	£1,152.00
5	product 1	6	£1,040.00	£1,248.00	£1,497.60
6	product 1	11	£1,352.00	£1,622.40	£1,946.88
7	product 2	2	£800.00	£960.00	£1,152.00
8	product 2	6	£1,040.00	£1,248.00	£1,497.60
9	product 2	11	£1,352.00	£1,622.40	£1,946.88
10	product 3	2	£800.00	£960.00	£1,152.00
11	product 3	6	£1,040.00	£1,248.00	£1,497.60
12	product 3	11	£1,352.00	£1,622.40	£1,946.88
13	product 4	2	£1,000.00	£1,200.00	£1,440.00
14	product 5	2	£1,000.00	£1,200.00	£1,440.00
15	product 6	2	£650.00	£650.00	£650.00
16	product 6	3	£650.00	£650.00	£650.00
17	product 7	2	£650.00	£650.00	£650.00
18	product 7	3	£650.00	£650.00	£650.00
19	product 7	4	£650.00	£650.00	£650.00
20	product 8	2	£650.00	£650.00	£650.00
2018 pricing

You could use

Excel 2013/2016
	A	B	C	D	E
1	Customer	Price	Product	Box Type (min 2)	Number of Boxes (min 4)
2	001	£800.00	product 2	2	4
3	002	£1,440.00	product 4	4	25
4	003	£800.00	product 3	4	10
5	004	£1,152.00	product 1	2	22
6	005	£800.00	product 2	2	10
7	006	£800.00	product 3	2	5
8	007	£800.00	product 1	7	10
9	008	£800.00	product 1	4	10
expenditure

Cell Formulas
Range	Formula
B2	{=INDEX('2018 pricing'!$C$4:$E$20,MATCH(1,('2018 pricing'!$A$4:$A$20=C2)*('2018 pricing'!$B$4:$B$20<=D2),0),MATCH(E2,'2018 pricing'!$C$3:$E$3,1))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Also at the moment your Expenditure table is way bigger than it needs to be. I'd suggest deleting all the extra blanks rows from the bottom, before using the above formula

 

[paul w]

thanks for your quick reply,
I changed the look up as you suggested, then copy and pasted the formula into expenditure after deleting all those empty rows,(don't know how the got there)
every result returns a #N/A error I'm assuming that it hasn't found a match. I don't know enough about these
functions to try and see what it is doing. could it be because I'm using excel 2007?
any suggestions on what to try would be great.

many thanks

paul

 

[Fluff]

If you look in the formula bar is the formula surrounded by {}, if not select the cell, press F2 and then Ctrl Shift Enter.
That should add the braces{} Then copy down.

 

[paul w]

Thanks for your time with this, very much appreciated,
I think its solved , the formatting between sheets was all to pot, expenditure was formatted as number, but price B4:B20 was text,
reformatted and tried [isnumber] but still showed false so had to re type each number back in, then it was true.

Not important right now but is there a way of adding more products to the price sheet, and the formula in expenditure changes accordingly, I've tried doing this by changing the ranges to include the extra row,and adding "product 9" to the data validation drop down, but it returns either a #value error or #NA error.

 

[paul w]

ref my query above about the #value and #N/A errors , when I changed the ranges to include the new row I forgot to re-enter as an array, oops,
very new to this level of excel, but always learning.
regarding the formula changing to suit the new rows added, its not a major problem as its a lot easier to change this formula to suit, than the 13 nested if formula.

again many thanks for your help and advice.

paul

 

[Fluff]

You can extend the formula like
=INDEX('2018 pricing'!$C$4:$E$30,MATCH(1,('2018 pricing'!$A$4:$A$30=C2)*('2018 pricing'!$B$4:$B$30<=D2),0),MATCH(E2,'2018 pricing'!$C$3:$E$3,1))
to cover extra rows, but it's best not to extend it too far beyond what you think will be the maximum number of rows you think you'll need.

 

[paul w]

Thanks for that, I thought I would try that and seen it would work,
while transferring this formula to the master sheet that I will be working from, I noticed it returns the wrong prices for certain scenarios, for example if I select (product 1) with (box type 9) and (number of boxes 16), it returns £960.00 but it should be £1240.00.

could it be because the data isn't sorted, IE. that column A has 3x product 1,3x product 2 and 3x product 3 etc, and its finding the first row to contain "product 1" and matching across from there.

I've been trying to work through how I think the formula works, but cant find a solution, any suggestions would be much appreciated, but I fear its because my lookup data isn't sorted. and I cant see a way around that , other than trying to making the product names unique or some sort of helper column maybe

 

[Fluff]

You'll need to sort the Price table highest to lowest on col B like

Excel 2013/2016
	A	B	C	D	E
1	Product	Box Type	2018
2			Number of Boxes
3			4	11	22
4	product 1	11	£1,352.00	£1,622.40	£1,946.88
5	product 1	6	£1,040.00	£1,248.00	£1,497.60
6	product 1	2	£800.00	£960.00	£1,152.00
7	product 2	11	£1,352.00	£1,622.40	£1,946.88
8	product 2	6	£1,040.00	£1,248.00	£1,497.60
9	product 2	2	£800.00	£960.00	£1,152.00
10	product 3	11	£1,352.00	£1,622.40	£1,946.88
11	product 3	6	£1,040.00	£1,248.00	£1,497.60
12	product 3	2	£800.00	£960.00	£1,152.00
13	product 4	2	£1,000.00	£1,200.00	£1,440.00
14	product 5	2	£1,000.00	£1,200.00	£1,440.00
15	product 6	3	£650.00	£650.00	£650.00
16	product 6	2	£650.00	£650.00	£650.00
17	product 7	4	£650.00	£650.00	£650.00
18	product 7	3	£650.00	£650.00	£650.00
19	product 7	2	£650.00	£650.00	£650.00
20	product 8	2	£650.00	£650.00	£650.00
2018 pricing

 

[paul w]

Many thanks, im not at the computer, so will try this tomorrow.