Adding an argument to a formula for three lookup arrays?

[BraydenMSB]

This is my current formula, the arrays are totally fine and the indexing is good.
=IF(B3<=24,INDEX($AQ$3:$BG$13,MATCH(B5,$AP$3:$AP$13,0),MATCH(B4,$AQ$2:$BG$2,-1)),IF(B3<=30,INDEX($AQ$31:$BG$41,MATCH(B5,$AP$3:$AP$13,0),MATCH(B4,$AQ$2:$BG$2,-1)),IF(B3>30,INDEX($AQ$59:$BG$69,MATCH(B5,$AP$3:$AP$13,0),MATCH(B4,$AQ$2:$BG$2,-1)),0)))

I need to add an argument to check if B17="Yes-Coastal"
For each of the index points, all of which are different widths.

and if so it needs to double the normal index BUT only on certain heights (13 and 14 feet). I also need to add a check for 15-16 foot, because these cannot be added on the coastal condition. This one has me stumped, anyone else have any insight? I will attach the arrays here. l've, just been scratching my head on this one

 

[cyrilbrd]

Hi,
Have you tried something along
=IF(AND(B17="Yes-Coastal",RC1="13'",RC2="14'"),then formula*2,then formula)

 

[cyrilbrd]

Here is an alternative with only one DB for the costing.
The data is not accurate so it will need to be validated.
It is assuming that the values for Width, Height and Length are exact.

excel.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Width	Height	Length	Type		Amount
2	24	14	101	yes-coastal		5100
3
4	Width	Height	101	96	91	86	81	76	71	66	61	56	51	46	41	36	31	26	21
5	24	16	3175	3035	2895	2755	2615	2475	2335	2195	2055	1915	1775	1635	1495	1355	1215	1075	935
6	24	15	2860	2735	2610	2485	2360	2235	2110	1985	1860	1735	1610	1485	1360	1235	1110	985	860
7	24	14	2550	2445	2340	2235	2130	2025	1920	1815	1710	1605	1500	1395	1290	1185	1080	975	870
8	24	13	2240	2155	2070	1985	1900	1815	1730	1645	1560	1475	1390	1305	1220	1135	1050	965	880
9	24	12	1920	1845	1770	1695	1620	1545	1470	1395	1320	1245	1170	1095	1020	945	870	795	720
10	24	11	1600	1535	1470	1405	1340	1275	1210	1145	1080	1015	950	885	820	755	690	625	560
11	24	10	1280	1225	1170	1115	1060	1005	950	895	840	785	730	675	620	565	510	455	400
12	24	9	955	920	885	850	815	780	745	710	675	640	605	570	535	500	465	430	395
13	24	8	640	620	600	580	560	540	520	500	480	460	440	420	400	380	360	340	320
14	24	7	320	310	300	290	280	270	260	250	240	230	220	210	200	190	180	170	160
15	24	6	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
16	30	16	4763	4553	4343	4133	3923	3713	3503	3293	3083	2873	2663	2453	2243	2033	1823	1613	1403
17	30	15	4290	4103	3915	3728	3540	3353	3165	2978	2790	2603	2415	2228	2040	1853	1665	1478	1290
18	30	14	3825	3668	3510	3353	3195	3038	2880	2723	2565	2408	2250	2093	1935	1778	1620	1463	1305
19	30	13	3360	3233	3105	2978	2850	2723	2595	2468	2340	2213	2085	1958	1830	1703	1575	1448	1320
20	30	12	2880	2768	2655	2543	2430	2318	2205	2093	1980	1868	1755	1643	1530	1418	1305	1193	1080
21	30	11	2400	2303	2205	2108	2010	1913	1815	1718	1620	1523	1425	1328	1230	1133	1035	938	840
22	30	10	1920	1838	1755	1673	1590	1508	1425	1343	1260	1178	1095	1013	930	848	765	683	600
23	30	9	1433	1380	1328	1275	1223	1170	1118	1065	1013	960	908	855	803	750	698	645	593
24	30	8	960	930	900	870	840	810	780	750	720	690	660	630	600	570	540	510	480
25	30	7	480	465	450	435	420	405	390	375	360	345	330	315	300	285	270	255	240
26	30	6	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
27	40	16	7144	6829	6514	6199	5884	5569	5254	4939	4624	4309	3994	3679	3364	3049	2734	2419	2104
28	40	15	6435	6154	5873	5591	5310	5029	4748	4466	4185	3904	3623	3341	3060	2779	2498	2216	1935
29	40	14	5738	5501	5265	5029	4793	4556	4320	4084	3848	3611	3375	3139	2903	2666	2430	2194	1958
30	40	13	5040	4849	4658	4466	4275	4084	3893	3701	3510	3319	3128	2936	2745	2554	2363	2171	1980
31	40	12	4320	4151	3983	3814	3645	3476	3308	3139	2970	2801	2633	2464	2295	2126	1958	1789	1620
32	40	11	3600	3454	3308	3161	3015	2869	2723	2576	2430	2284	2138	1991	1845	1699	1553	1406	1260
33	40	10	2880	2756	2633	2509	2385	2261	2138	2014	1890	1766	1643	1519	1395	1271	1148	1024	900
34	40	9	2149	2070	1991	1913	1834	1755	1676	1598	1519	1440	1361	1283	1204	1125	1046	968	889
35	40	8	1440	1395	1350	1305	1260	1215	1170	1125	1080	1035	990	945	900	855	810	765	720
36	40	7	720	698	675	653	630	608	585	563	540	518	495	473	450	428	405	383	360
37	40	6	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
BraydenMSB

Cell Formulas
Range	Formula
F2	F2	=IF(AND(D2="yes-coastal",OR(B2=15,B2=16)),"check",IF(AND(D2="yes-coastal",OR(B2=13,B2=14)),SUMPRODUCT((A5:A37=A2)*(B5:B37=B2)*(C4:S4=C2),C5:S37)*2,SUMPRODUCT((A5:A37=A2)*(B5:B37=B2)*(C4:S4=C2),C5:S37)))

 

[BraydenMSB]

Here is an alternative with only one DB for the costing.
The data is not accurate so it will need to be validated.
It is assuming that the values for Width, Height and Length are exact.

excel.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Width	Height	Length	Type		Amount
2	24	14	101	yes-coastal		5100
3
4	Width	Height	101	96	91	86	81	76	71	66	61	56	51	46	41	36	31	26	21
5	24	16	3175	3035	2895	2755	2615	2475	2335	2195	2055	1915	1775	1635	1495	1355	1215	1075	935
6	24	15	2860	2735	2610	2485	2360	2235	2110	1985	1860	1735	1610	1485	1360	1235	1110	985	860
7	24	14	2550	2445	2340	2235	2130	2025	1920	1815	1710	1605	1500	1395	1290	1185	1080	975	870
8	24	13	2240	2155	2070	1985	1900	1815	1730	1645	1560	1475	1390	1305	1220	1135	1050	965	880
9	24	12	1920	1845	1770	1695	1620	1545	1470	1395	1320	1245	1170	1095	1020	945	870	795	720
10	24	11	1600	1535	1470	1405	1340	1275	1210	1145	1080	1015	950	885	820	755	690	625	560
11	24	10	1280	1225	1170	1115	1060	1005	950	895	840	785	730	675	620	565	510	455	400
12	24	9	955	920	885	850	815	780	745	710	675	640	605	570	535	500	465	430	395
13	24	8	640	620	600	580	560	540	520	500	480	460	440	420	400	380	360	340	320
14	24	7	320	310	300	290	280	270	260	250	240	230	220	210	200	190	180	170	160
15	24	6	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
16	30	16	4763	4553	4343	4133	3923	3713	3503	3293	3083	2873	2663	2453	2243	2033	1823	1613	1403
17	30	15	4290	4103	3915	3728	3540	3353	3165	2978	2790	2603	2415	2228	2040	1853	1665	1478	1290
18	30	14	3825	3668	3510	3353	3195	3038	2880	2723	2565	2408	2250	2093	1935	1778	1620	1463	1305
19	30	13	3360	3233	3105	2978	2850	2723	2595	2468	2340	2213	2085	1958	1830	1703	1575	1448	1320
20	30	12	2880	2768	2655	2543	2430	2318	2205	2093	1980	1868	1755	1643	1530	1418	1305	1193	1080
21	30	11	2400	2303	2205	2108	2010	1913	1815	1718	1620	1523	1425	1328	1230	1133	1035	938	840
22	30	10	1920	1838	1755	1673	1590	1508	1425	1343	1260	1178	1095	1013	930	848	765	683	600
23	30	9	1433	1380	1328	1275	1223	1170	1118	1065	1013	960	908	855	803	750	698	645	593
24	30	8	960	930	900	870	840	810	780	750	720	690	660	630	600	570	540	510	480
25	30	7	480	465	450	435	420	405	390	375	360	345	330	315	300	285	270	255	240
26	30	6	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
27	40	16	7144	6829	6514	6199	5884	5569	5254	4939	4624	4309	3994	3679	3364	3049	2734	2419	2104
28	40	15	6435	6154	5873	5591	5310	5029	4748	4466	4185	3904	3623	3341	3060	2779	2498	2216	1935
29	40	14	5738	5501	5265	5029	4793	4556	4320	4084	3848	3611	3375	3139	2903	2666	2430	2194	1958
30	40	13	5040	4849	4658	4466	4275	4084	3893	3701	3510	3319	3128	2936	2745	2554	2363	2171	1980
31	40	12	4320	4151	3983	3814	3645	3476	3308	3139	2970	2801	2633	2464	2295	2126	1958	1789	1620
32	40	11	3600	3454	3308	3161	3015	2869	2723	2576	2430	2284	2138	1991	1845	1699	1553	1406	1260
33	40	10	2880	2756	2633	2509	2385	2261	2138	2014	1890	1766	1643	1519	1395	1271	1148	1024	900
34	40	9	2149	2070	1991	1913	1834	1755	1676	1598	1519	1440	1361	1283	1204	1125	1046	968	889
35	40	8	1440	1395	1350	1305	1260	1215	1170	1125	1080	1035	990	945	900	855	810	765	720
36	40	7	720	698	675	653	630	608	585	563	540	518	495	473	450	428	405	383	360
37	40	6	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
BraydenMSB

Cell Formulas
Range	Formula
F2	F2	=IF(AND(D2="yes-coastal",OR(B2=15,B2=16)),"check",IF(AND(D2="yes-coastal",OR(B2=13,B2=14)),SUMPRODUCT((A5:A37=A2)*(B5:B37=B2)*(C4:S4=C2),C5:S37)*2,SUMPRODUCT((A5:A37=A2)*(B5:B37=B2)*(C4:S4=C2),C5:S37)))

I like this idea, but I would have to adjust an entire pricing form if I change to a singular data index. Where would be the best place within my original formula to insert your earlier suggestion with the namecheck option? I'm not sure where that argument best fits

 

[cyrilbrd]

Keep that bit =IF(AND(D2="yes-coastal",OR(B2=15,B2=16)),"check",IF(AND(D2="yes-coastal",OR(B2=13,B2=14)), then here add your formulae followed by *2, then here put again your formulae followed by ))
Adjust the references to fit your model.

 

[BraydenMSB]

Keep that bit =IF(AND(D2="yes-coastal",OR(B2=15,B2=16)),"check",IF(AND(D2="yes-coastal",OR(B2=13,B2=14)), then here add your formulae followed by *2, then here put again your formulae followed by ))
Adjust the references to fit your model.

Got it, thank you!

 

[cyrilbrd]

Most welcome. Thanks for your feedback.