Another IndexMatch thread

[nitrobass24]

Having issues with my index match formula and i've even tried the filter function, but going back to what I thought I knew IndexMatch, its just pulling an answer and I have no idea why.

Table

Book1
	I	J	K	L	M	N	O	P
5	Manufacturer	Size	Dumpster cost	Decal Cost	Decal Install	Ext Cost	Truckload	Total Cost
6	Boxgang	20 Std	$ 5,000	$ 450	$ 200	$ 5,650	9	$ 50,850
7	Elite Dumpster	20 Std	$ 5,137	$ 450	$ 200	$ 5,787	9	$ 52,083
8	EZFab	20 Std	$ 7,725			$ 7,725	8	$ 61,800
9	Keystone	20 Std	$ 5,026	$ 450	$ 200	$ 5,676	9	$ 51,084
10	Boxgang	30 Std		$ 450	$ 200	$ 650	9	$ 5,850
11	Elite Dumpster	30 Std	$ 5,642	$ 450	$ 200	$ 6,292	9	$ 56,628
12	EZFab	30 Std	$ 7,975			$ 7,975	8	$ 63,800
13	Keystone	30 Std	$ 5,572	$ 450	$ 200	$ 6,222	9	$ 55,998
14	Boxgang	40 Std		$ 450	$ 200	$ 650	5	$ 3,250
15	Elite Dumpster	40 Std	$ 6,142	$ 450	$ 200	$ 6,792	5	$ 33,960
16	EZFab	40 Std	$ 9,595			$ 9,595	4	$ 38,380
17	Keystone	40 Std	$ 6,650	$ 450	$ 200	$ 7,300	5	$ 36,500
Dumpster Pricing

Cell Formulas
Range	Formula
N6:N17	N6	=SUM(K6:M6)
P6:P17	P6	=[@Truckload]*[@[Ext Cost]]

Calculaor

Book1
	C	D
2	Assumptions
3	Shipping Disc.	4000
4	Royalty Discount (months)	4
5	Royalty Rate	17
6	Manufacturer	Boxgang
7	Dumpster	20 Std
8	Truckload Costs	#N/A
Revenue Calculator

Cell Formulas
Range	Formula
D8	D8	=INDEX(PriceSheet[Total Cost], MATCH(1, (C6=PriceSheet[Manufacturer])*(C7=PriceSheet[Size]),0))

Cells with Data Validation
Cell	Allow	Criteria
D5	List	=$D$21:$D$26
D6	List	='DV Fields'!$A$2:$A$5
D7	List	='DV Fields'!$B$2:$B$4

formula I am using
=INDEX(PriceSheet[Total Cost], MATCH(1, (C6=PriceSheet[Manufacturer])*(C7=PriceSheet[Size]),0))

 

[kweaver]

Rather than C6 and C7, don't you mean D6 and D7? which then yields: 50850 ?

 

[awoohaw]

works for me:

mr excel questions 24.xlsm
									I	J	K	L	M	N	O	P	Q	R	S
1									Manufacturer	Size	Dumpster cost	Decal Cost	Decal Install	Ext Cost	Truckload	Total Cost		Assumptions
2									Boxgang	20 Std	5000	450	200	5650	9	50850		Shipping Disc.	4000
3									Elite Dumpster	20 Std	5137	450	200	5787	9	52083		Royalty Discount (months)	4
4									EZFab	20 Std	7725			7725	8	61800		Royalty Rate	17
5									Keystone	20 Std	5026	450	200	5676	9	51084		Manufacturer	Boxgang
6									Boxgang	30 Std		450	200	650	9	5850		Dumpster	20 Std
7									Elite Dumpster	30 Std	5642	450	200	6292	9	56628		Truckload Costs	50850
8									EZFab	30 Std	7975			7975	8	63800
9									Keystone	30 Std	5572	450	200	6222	9	55998
10									Boxgang	40 Std		450	200	650	5	3250
11									Elite Dumpster	40 Std	6142	450	200	6792	5	33960
12									EZFab	40 Std	9595			9595	4	38380
13									Keystone	40 Std	6650	450	200	7300	5	36500
14
nitrobass24

Cell Formulas
Range	Formula
S7	S7	=INDEX(Table3[Total Cost], MATCH(1, (S5=Table3[Manufacturer])*(S6=Table3[Size]),0))
N2:N13	N2	=SUM(Table3[@[Dumpster cost]:[Decal Install]])
P2:P13	P2	=[@Truckload]*[@[Ext Cost]]

 

[nitrobass24]

Rather than C6 and C7, don't you mean D6 and D7? which then yields: 50850 ?

Thank you I knew it was something stupid...just looking at this for too long.

 

[kweaver]

Not a problem. We all do that. Please mark it solve so that someone else doesn't dig around for an issue.