Complicated Index formula with OR not working

[oblix]

Hi there all

I have a complicated index formula I can not resolve.

Background:

I created a sheet with a shopping list (sheet name - SL1)
in this sheet
column B has the product description (eg apples)
column Q the name of the company I entend to buy from (eg company A)
column R the oty i intend buying (eg 10)
column S the price of product for this company (eg $10-00)

in onother sheet i created a purchase order form with these formulas (sheet name PO1)
column B product decription
=IFERROR(INDEX('SL1'!$B$7:$B$605,SMALL(IF(('SL1'!$R$7:$R$605>0)*('SL1'!$Q$7:$Q$605=$K$5),ROW('SL1'!$B$7:$B$605)),ROW(1:1))-6,1),"")

column E Qty: =IFERROR(ROUNDUP(INDEX('SL1'!$R$7:$R$489,MATCH(A20,'SL1'!$B$7:$B$489,0)),0),"")

column F price: =IF(ISERROR(MATCH(A20,'SL1'!$B$7:$B$489,0)),"",INDEX('SL1'!$S$7:$S$489,MATCH(A20,'SL1'!$B$7:$B$489,0)))

k5 = name of company I want to order from

This formulas worked beautifully and was able to drag formulas down the columns ad there is my order form.
If I change the name of the company (k5), the order updates for that company.

Problem:
The boss asked me to add columns in the shopping list to add another possible company per product line.
I did this by adding:
column U: Company name 2
column V: QTY 2
column W: Price 2

Now the purchase order needs to look at two set of columns (q,r,s) and (U,V,W) to populate as company of that order can be in one of those column groups.


For the life of me I dont know how to ammend those three formulas to make this work and believe I have tried.
Please please is there any body that can assist!

Thank you in advance

 

[jasonb75]

This would be a lot easier to resolve if you used the XL2BB addin (see link in my signature below) to post an example of your data (fictionalise any confidential info) along with the expected results.

It would also help if you update your profile to show which version of excel you are using, to do this, click on your user name at the top right of the page then 'Account details'. Scroll down and check the box for the correct version of excel, then scroll to the bottom and save changes.

 

[oblix]

03_Monthly_Shopping_Master.xlsm
	K
11
PO_1

 

[oblix]

03_Monthly_Shopping_Master.xlsm
	A	B															Q	R	S	T	U	V	W	X	Y	Z
6	Ser No.	RATION ITEM															SUPPLIER 1	QTY 1	PRICE PER DOQ	COMP 1 SUB TOTAL	SUPPLIER 2	QTY 2	PRICE PER DOQ	COMP 2 SUB TOTAL	TOTAL	Taxable
7
8	x	x															x		x	x	x		x	x	x	x
9	A	STARCH WEIGHT PER MEAL																		R 112.38		6		R 87.88	R 170.00	1
10	A001	Corn Meal (Mielie Meal)															G/ Catering	0	R 19.85	R -	G/ Catering	0	R 19.85	R -	R -	x
11	A002	Instant Potato Powder																0				0				x
12	A003	Lasagne Sheets															G/ Catering	0	R 18.56	R -		0				x
13	A004	Macaroni															MALESELA	0	R 10.68	R -		0				x
14	A005	Noodle Shells															C/ House	0	R 15.09	R -		0				x
15	A006	Noodles Ribbon															Bidfood	0	R 14.26	R -		0				x
16	A007	Potato Chips Frozen 10mm															Bidfood	0	R 20.14	R -		0				x
17	A008	Potato Chips Frozen Oven Bake																0				0				x
18	A009	Potato Fresh Pre-Prepared															VANANI	2	R 10.50	R 18.38	VANANI	2	R 10.50	R 18.38	R 36.75	x
19	A010	Potatoes Fresh															VANANI	0	R 85.00	R -		0				x
20	A011	Rice Parboiled Brown																0			GOOD MANNERS	0	R 24.20	R -		x
21	A012	Rice Parboiled White															GOOD MANNERS	1	R 24.20	R 30.25		0				x
22	A013	SAMP															Mistro	0	R 17.50	R -		0				x
23	A014	Spaghetti															C/ House	5	R 12.75	R 63.75	Bidfood	5	R 13.90	R 69.50	R 133.25	x
24	A015	Vetkoek Plain Medium																0				0				x
25	A016	Wraps Plain Medium																0				0				x
SL1

Cell Formulas
Range	Formula
T9,X9:Y9	T9	=SUM(T10:T25)
A10:B25	A10	='SHOPPING LIST'!A10
Q10:Q25,W10:W25,U10:U25,S10:S25	Q10	=+'SHOPPING LIST'!AV10
R10:R25	R10	=+K10*'C:\Users\Marsau\Desktop\Master V1\Master V1 - TEST\Master Rations\[02_Price_Comparison_Master.xlsm]COM DECISION'!I10
T10:T25,X10:X25	T10	=IFERROR(R10*S10,"")
V10:V25	V10	=+K10*'C:\Users\Marsau\Desktop\Master V1\Master V1 - TEST\Master Rations\[02_Price_Comparison_Master.xlsm]COM DECISION'!L10
Y10:Y25	Y10	=IFERROR(X10+T10," ")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
X24:X25	Expression	=AND($A22<>"""",#REF!=ROW())	text	NO
U24:W25,Y24:Y25	Expression	=AND($A22<>"""",#REF!=ROW())	text	NO
F24:R25,T24:T25	Expression	=AND($A22<>"""",#REF!=ROW())	text	NO
Z9	Expression	=AND($A8<>"""",#REF!=ROW())	text	NO
Q607,A9:Z605	Expression	=AND($A9<>"""",$AA$9=ROW())	text	NO

 

[oblix]

03_Monthly_Shopping_Master.xlsm
	A	B	C	D	E	F	G	H
17
18
19	DESCRIPTION		Unit Size		QTY	UNIT PRICE	VAT	TOTAL
20	Spaghetti		500.0	g	5	R 12.75	x	R 63.75
21	Margarine		1	ea	5	R 15.00	x	R 75.00
22	Peanut Butter Portions (15G)		15.000	g	20000	R 31.17	x	R 623,400.00
23
24
25
PO_1

Cell Formulas
Range	Formula
C20:C25	C20	=IF(ISERROR(MATCH(A20,'SL1'!$B$7:$B$489,0)),"",INDEX('SL1'!$O$7:$O$489,MATCH(A20,'SL1'!$B$7:$B$489,0)))
D20:D25	D20	=IF(ISERROR(MATCH(A20,'SL1'!$B$7:$B$489,0)),"",INDEX('SL1'!$P$7:$P$489,MATCH(A20,'SL1'!$B$7:$B$489,0)))
E20	E20	=IFERROR(ROUNDUP(INDEX('SL1'!$R$7:$R$489,MATCH(A20,'SL1'!$B$7:$B$489,0)),0),"")
F20:F25	F20	=IF(ISERROR(MATCH(A20,'SL1'!$B$7:$B$489,0)),"",INDEX('SL1'!$S$7:$S$489,MATCH(A20,'SL1'!$B$7:$B$489,0)))
G20:G25	G20	=IF(ISERROR(MATCH(A20,'SL1'!$B$7:$B$489,0)),"",INDEX('SL1'!$Z$7:$Z$489,MATCH(A20,'SL1'!$B$7:$B$489,0)))
H20:H25	H20	=IF(E20="","",E20*F20)
E21:E25	E21	=IFERROR(ROUNDUP(INDEX('SL1'!$K$7:$K$489,MATCH(A21,'SL1'!$B$7:$B$489,0)),0),"")
A20:A25	A20	=IFERROR(INDEX('SL1'!$B$7:$B$605,SMALL(IF(('SL1'!$R$7:$R$605>0)*('SL1'!$Q$7:$Q$605=$K$5),ROW('SL1'!$B$7:$B$605)),ROW(1:1))-6,1),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

Named Ranges
Name	Refers To	Cells
Print_Area	=PO_1!$A$4:$H$274	A23

 

[jasonb75]

Try this, none of the formulas need to be array confirmed.

Book 12.xlsx
	A	B	C	D	E	F	G	H
18
19	DESCRIPTION		Unit Size		QTY	UNIT PRICE	VAT	TOTAL
20	Potato Fresh Pre-Prepared		0	0	2	10.5	x	21
21	Potato Fresh Pre-Prepared		0	0	2	10.5	x	21
22
23
24
25
PO_1

Cell Formulas
Range	Formula
C20:C25	C20	=IFERROR(INDEX('SL1'!$O$7:$O$489,MATCH(A20,'SL1'!$B$7:$B$489,0)),"")
D20:D25	D20	=IFERROR(INDEX('SL1'!$P$7:$P$489,MATCH(A20,'SL1'!$B$7:$B$489,0)),"")
E20:E25	E20	=IFERROR(AGGREGATE(15,6,'SL1'!$R$7:$V$605/('SL1'!$R$7:$V$605>0)/('SL1'!$Q$7:$U$605=$K$5),ROWS(A$20:A20)),"")
F20:F25	F20	=IFERROR(ROUNDUP(AGGREGATE(15,6,'SL1'!$S$7:$W$605/('SL1'!$R$7:$V$605>0)/('SL1'!$Q$7:$U$605=$K$5),ROWS(A$20:A20)),2),"")
G20:G25	G20	=IFERROR(INDEX('SL1'!$Z$7:$Z$489,MATCH(A20,'SL1'!$B$7:$B$489,0)),"")
H20:H25	H20	=IF(E20="","",E20*F20)
A20:A25	A20	=IFERROR(INDEX('SL1'!$B:$B,AGGREGATE(15,6,ROW('SL1'!$B$7:$B$605)/('SL1'!$R$7:$V$605>0)/('SL1'!$Q$7:$U$605=$K$5),ROWS(A$20:A20))),"")

 

[oblix]

Thank you for assitance.
A20 formula in PO1 working great. But Qty (E20) and Price not giving the correct answer.
Please assist

 

[jasonb75]

Could you post an example with the incorrect results? As far as I can see the formulas are working correctly, if they are not then it means that there could be something in your data that I have not allowed for in the formula.

 

[oblix]

03_Monthly_Shopping_Master.xlsm
	A	B	C	D	E	F	G	H
18
19	DESCRIPTION		Unit Size		QTY	UNIT PRICE	VAT	TOTAL
20	Corn Meal (Mielie Meal)		2.5	kg	0	R 1.41	x	R 0.56
21	Lasagne Sheets		500.0	g	1	R 14.50	x	R 11.60
22	Syrup		500.0	g	1	R 14.75	x	R 14.75
23	Bacon Sliced Shoulder (B)		1.0	kg	5	R 18.49	x	R 88.75
24	Chicken Liver (B)		1.0	kg	5	R 18.56	x	R 92.80
25	Chicken Nuggets (B)		400.0	g	8	R 18.89	x	R 151.12
26	Fish Fingers Frozen (B)		2.5	kg	15	R 19.85	x	R 297.75
27	Corn Meal (Mealie Meal) (B)		2.5	kg	15	R 19.85	x	R 297.75
28	Cereal Puffed Rice		2.0	kg	21	R 28.68	x	R 602.28
29	Chicken Breast Portions		1.0	kg	24	R 40.83	0	R 979.92
30	Chicken Breasts Fillet		1.0	kg	35	R 43.49	0	R 1,522.15
31	Chicken Steaklets 100G		1.0	kg	40	R 73.09	0	R 2,923.60
32	Broccoli Frozen		1.0	kg	40	R 75.77	0	R 3,030.80
33	French Style Mix Frozen		1.0	kg	50	R 153.30	0	R 7,665.00
34	Gravy Powder		1.0	kg	50	R 153.62	0	R 7,681.00
35	Vinegar		2.5	kg	60	R 167.86	0	R 10,071.60
36	Peri Peri Ground		1.0	kg	78	R 180.73	0	R 14,096.94
37
38
39
40
41					[42]	SUBTOTAL		R 49,528.38
42	Comments or Special Instructions					TAXABLE		R 1,557.37
43						TAX RATE		14%
44						TAX		R 218.03
45						SHIPPING		R -
46						OTHER		R -
47						TOTAL		R 49,746.41
PO_1

Cell Formulas
Range	Formula
C20:C40	C20	=IFERROR(INDEX('SL1'!$O$7:$O$489,MATCH(A20,'SL1'!$B$7:$B$489,0)),"")
D20:D40	D20	=IFERROR(INDEX('SL1'!$P$7:$P$489,MATCH(A20,'SL1'!$B$7:$B$489,0)),"")
E20	E20	=IFERROR(AGGREGATE(15,6,('SL1'!$R$7:$V$605)/('SL1'!$R$7:$V$605>0)/('SL1'!$Q$7:$U$605=$K$5),ROWS(A$20:A20)),"")
F20:F40	F20	=IFERROR(ROUNDUP(AGGREGATE(15,6,'SL1'!$S$7:$W$605/('SL1'!$R$7:$V$605>0)/('SL1'!$Q$7:$U$605=$K$5),ROWS(A$20:A20)),2),"")
G20:G40	G20	=IFERROR(INDEX('SL1'!$Z$7:$Z$489,MATCH(A20,'SL1'!$B$7:$B$489,0)),"")
E21:E40	E21	=IFERROR(AGGREGATE(15,6,'SL1'!$R$7:$V$605/('SL1'!$R$7:$V$605>0)/('SL1'!$Q$7:$U$605=$K$5),ROWS(A$20:A21)),"")
A20:A40	A20	=IFERROR(INDEX('SL1'!$B:$B,AGGREGATE(15,6,ROW('SL1'!$B$7:$B$605)/('SL1'!$R$7:$V$605>0)/('SL1'!$Q$7:$U$605=$K$5),ROWS(A$20:A20))),"")
H20:H40	H20	=IF(E20="","",E20*F20)
H41	H41	=SUM(H20:H40)
H42	H42	=SUMIF(G20:G40,"=x",H20:H40)
H44	H44	=H43*H42
H47	H47	=H41+H44+H46+H45

 

[oblix]

03_Monthly_Shopping_Master.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L			O	P	Q	R	S	T	U	V	W	X	Y	Z
6	Ser No.	RATION ITEM	ISSUE SCALE	Kg/ Lt/ ea	Total Issue Pattern	ITEMS REQUIRED	UOM	STOCK ON HAND	AMOUNT TO BE DEMANDED	UOM	QTY	X			ITEM PACKAGING5	UOM	SUPPLIER 1	QTY 1	PRICE PER DOQ	COMP 1 SUB TOTAL	SUPPLIER 2	QTY 2	PRICE PER DOQ	COMP 2 SUB TOTAL	TOTAL	Taxable
7
8	x	x	x	x	x	x	x	x	x	x	x	x			x	x	x		x	x	x		x	x	x	x
9	A	STARCH WEIGHT PER MEAL			60						x									R 3,783.70		6		R 2,481.45	R 666.25	1
10	A001	Corn Meal (Mielie Meal)	0.125	kg	12.0 meals	150	kg		150	kg	60	X			2.5	kg		0			G/ Catering	60	R 19.85	R 1,191.00		x
11	A002	Instant Potato Powder	0.150	kg	0.0 meals	0	kg		-	kg	-	X			2	kg		0				0				x
12	A003	Lasagne Sheets	0.125	kg	2.0 meals	25	kg		25	kg	50	X			500	g	G/ Catering	50	R 18.56	R 928.00		0				x
13	A004	Macaroni	0.050	kg	2.5 meals	13	kg		13	kg	25	X			500	g	MALESELA	25	R 10.68	R 267.00		0				x
14	A005	Noodle Shells	0.050	kg	1.0 meals	5	kg		5	kg	10	X			500	g	C/ House	10	R 15.09	R 150.90		0				x
15	A006	Noodles Ribbon	0.050	kg	1.0 meals	5	kg		5	kg	10	X			500	g	Bidfood	10	R 14.26	R 142.60		0				x
16	A007	Potato Chips Frozen 10mm	0.150	kg	5.0 meals	75	kg		75	kg	30	X			2.5	kg	Bidfood	30	R 20.14	R 604.20		0				x
17	A008	Potato Chips Frozen Oven Bake	0.150	kg	1.0 meals	15	kg		15	kg	6	X			2.5	kg		6				0				x
18	A009	Potato Fresh Pre-Prepared	0.175	kg	12.5 meals	219	kg		219	kg	88	X			2.5	kg		0			VANANI	88	R 10.50	R 918.75		x
19	A010	Potatoes Fresh	0.200	kg	2.0 meals	40	kg		40	kg	4	X			10	kg	VANANI	4	R 85.00	R 340.00		0				x
20	A011	Rice Parboiled Brown	0.050	kg	0.5 meals	3	kg		3	kg	1	X			2.5	kg		0			GOOD MANNERS	1	R 24.20	R 24.20		x
21	A012	Rice Parboiled White	0.050	kg	12.0 meals	60	kg		60	kg	30	X			2	kg	GOOD MANNERS	30	R 24.20	R 726.00		0				x
22	A013	SAMP	0.125	kg	3.5 meals	44	kg		44	kg	18	X			2.5	kg	Mistro	18	R 17.50	R 306.25		0				x
23	A014	Spaghetti	0.050	kg	5.0 meals	25	kg		25	kg	50	X			500	g	C/ House	25	R 12.75	R 318.75	Bidfood	25	R 13.90	R 347.50	R 666.25	x
24	A015	Vetkoek Plain Medium	2.000	ea	0.0 meals	0	kg		-	kg	-	X			2.5	kg		0				0				x
25	A016	Wraps Plain Medium	2.000	ea	0.0 meals	0	kg		-	kg	-	X			2.5	kg		0				0				x
26	x	x	x	x	x	x	x	x	x	x	x	x			x	x	x		x	x	x		x	x	x	x
27	B	FAT WEIGHT PER DAY			30.0 meals						x									R 2,250.00				R -	R -
SL1

Cell Formulas
Range	Formula
E9,X9:Y9,T9	E9	=SUM(E10:E25)
A10:D25	A10	='SHOPPING LIST'!A10
E10:E25	E10	=SUMIFS('SHOPPING LIST'!E10:AI10,'SHOPPING LIST'!$E$6:$AI$6,">="&'SL1'!$L$4,'SHOPPING LIST'!$E$6:$AI$6,"<="&'SL1'!$S$4)
F10:F25	F10	=+C10*$F$4*E10
W10:W25,U10:U25,S10:S25,O10:Q25,J10:J25,G10:G25	G10	=+'SHOPPING LIST'!AL10
I10:I25	I10	=MAX(0,F10-(H10*M10))
K10:K25	K10	=IF(I10=0,0,+I10/M10)
R10:R25	R10	=+K10*'C:\Users\Marsau\Dropbox\My PC (DESKTOP-SVL8AIM)\Desktop\Master V2\Master V1 - TEST\Master Rations\[02_Price_Comparison_Master.xlsm]COM DECISION'!I10
T10:T25,X10:X25	T10	=IFERROR(R10*S10,"")
V10:V25	V10	=+K10*'C:\Users\Marsau\Dropbox\My PC (DESKTOP-SVL8AIM)\Desktop\Master V2\Master V1 - TEST\Master Rations\[02_Price_Comparison_Master.xlsm]COM DECISION'!L10
Y10:Y25	Y10	=IFERROR(X10+T10," ")
E27,X27:Y27,T27	E27	=SUM(E28:E31)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
X24:X25	Expression	=AND($A22<>"""",#REF!=ROW())	text	NO
U24:W25,Y24:Y25	Expression	=AND($A22<>"""",#REF!=ROW())	text	NO
E24:E25,Z24:Z25	Expression	=AND($A22<>"""",#REF!=ROW())	text	NO
F24:R25,T24:T25	Expression	=AND($A22<>"""",#REF!=ROW())	text	NO
Z27	Expression	=AND($A26<>"""",#REF!=ROW())	text	NO
Z9	Expression	=AND($A8<>"""",#REF!=ROW())	text	NO
Q607,A9:Z605	Expression	=AND($A9<>"""",$AA$9=ROW())	text	NO