Addition to INDEX formula to choose which table to reference

[bfuentes1412]

Hello. Looking to have the formulas in cells D5, E5, & K5 reference Table 1 or Table 2 based on the 'ZONE'. I tried combining both into one LONG table however, I can't figure out how to get the formula to look for 1, 3, 5, OR 7 independently for the 'ZONE' in table 1. If I need to keep the tables separate I'm totally good with that; combing would also be ok if need be. FYI, all formulas only reference table 1 with the exception of using info in column 'U' as criteria.

The goal is to place a drop down for the zone and have a selection for "1, 3, 5, 7, 9" and another drop down for EV "Yes" or "No". Based on those two choices the formulas in D5, E5 & K5 would use the appropriate table for the calculations. In the example, If Zone 3 is chosen, EV is 'NO', and the customer # is 47 the formulas should pull info from U57:Z57, as shown.

Please let me know if more info is needed. THANK YOU!

xfmr calculator.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
4	ZONE	EV	Adjust Design ft(2)	XFMR SIZE	Total # of Allowable Cust.	# of Customers	Additional Load	KVA PER CUST	MAX DWELLING UTILIZATION	TOTAL LOAD	Recommended Xfrm Size			ZONE	EV	sqft	xfmr	cust #	REMOVAL POINT		ZONE	EV	sqft	xfmr	cust #	REMOVAL POINT
5	3	NO	2600	75	81	47	10	1.11	52.22	62.22	75			9	YES	600	75	21	90		1, 3, 5, 7	YES	600	75	23	90
6														9	YES	600	150	42	180		1, 3, 5, 7	YES	600	150	46	180
7														9	YES	600	300	84	36		1, 3, 5, 7	YES	600	300	92	36
8														9	YES	600	500	140	600		1, 3, 5, 7	YES	600	500	153	600
9														9	YES	800	75	20	90		1, 3, 5, 7	YES	800	75	22	90
10														9	YES	800	150	40	180		1, 3, 5, 7	YES	800	150	44	180
11														9	YES	800	300	80	36		1, 3, 5, 7	YES	800	300	88	36
12														9	YES	800	500	133	600		1, 3, 5, 7	YES	800	500	147	600
13														9	YES	1000	75	20	90		1, 3, 5, 7	YES	1000	75	22	90
14														9	YES	1000	150	40	180		1, 3, 5, 7	YES	1000	150	44	180
15														9	YES	1000	300	80	36		1, 3, 5, 7	YES	1000	300	88	36
16														9	YES	1000	500	133	600		1, 3, 5, 7	YES	1000	500	147	600
17														9	YES	1200	75	20	90		1, 3, 5, 7	YES	1200	75	21	90
18														9	YES	1200	150	40	180		1, 3, 5, 7	YES	1200	150	42	180
19														9	YES	1200	300	80	36		1, 3, 5, 7	YES	1200	300	84	36
20														9	YES	1200	500	133	600		1, 3, 5, 7	YES	1200	500	140	600
21														9	YES	1400	75	19	90		1, 3, 5, 7	YES	1400	75	21	90
22														9	YES	1400	150	38	180		1, 3, 5, 7	YES	1400	150	42	180
23														9	YES	1400	300	76	36		1, 3, 5, 7	YES	1400	300	84	36
24														9	YES	1400	500	127	600		1, 3, 5, 7	YES	1400	500	140	600
25														9	YES	1600	75	19	90		1, 3, 5, 7	YES	1600	75	21	90
26														9	YES	1600	150	38	180		1, 3, 5, 7	YES	1600	150	42	180
27														9	YES	1600	300	76	36		1, 3, 5, 7	YES	1600	300	84	36
28														9	YES	1600	500	127	600		1, 3, 5, 7	YES	1600	500	140	600
29														9	YES	1800	75	19	90		1, 3, 5, 7	YES	1800	75	20	90
30														9	YES	1800	150	38	180		1, 3, 5, 7	YES	1800	150	40	180
31														9	YES	1800	300	76	36		1, 3, 5, 7	YES	1800	300	80	36
32														9	YES	1800	500	127	600		1, 3, 5, 7	YES	1800	500	133	600
33														9	YES	2000	75	18	90		1, 3, 5, 7	YES	2000	75	20	90
34														9	YES	2000	150	36	180		1, 3, 5, 7	YES	2000	150	40	180
35														9	YES	2000	300	72	36		1, 3, 5, 7	YES	2000	300	80	36
36														9	YES	2000	500	120	600		1, 3, 5, 7	YES	2000	500	133	600
37														9	YES	2200	75	18	90		1, 3, 5, 7	YES	2200	75	19	90
38														9	YES	2200	150	36	180		1, 3, 5, 7	YES	2200	150	38	180
39														9	YES	2200	300	72	36		1, 3, 5, 7	YES	2200	300	76	36
40														9	YES	2200	500	120	600		1, 3, 5, 7	YES	2200	500	127	600
41														9	YES	2400	75	17	90		1, 3, 5, 7	YES	2400	75	19	90
42														9	YES	2400	150	34	180		1, 3, 5, 7	YES	2400	150	38	180
43														9	YES	2400	300	68	36		1, 3, 5, 7	YES	2400	300	76	36
44														9	YES	2400	500	113	600		1, 3, 5, 7	YES	2400	500	127	600
45														9	YES	2600	75	15	90		1, 3, 5, 7	YES	2600	75	19	90
46														9	YES	2600	150	30	180		1, 3, 5, 7	YES	2600	150	38	180
47														9	YES	2600	300	60	36		1, 3, 5, 7	YES	2600	300	76	36
48														9	YES	2600	500	100	600		1, 3, 5, 7	YES	2600	500	127	600
49														9	NO	600	75	103	90		1, 3, 5, 7	NO	600	75	129	90
50														9	NO	600	150	206	180		1, 3, 5, 7	NO	600	150	258	180
51														9	NO	600	300	412	36		1, 3, 5, 7	NO	600	300	516	36
52														9	NO	600	500	687	600		1, 3, 5, 7	NO	600	500	860	600
53														9	NO	800	75	75	90		1, 3, 5, 7	NO	800	75	106	90
54														9	NO	800	150	150	180		1, 3, 5, 7	NO	800	150	212	180
55														9	NO	800	300	300	36		1, 3, 5, 7	NO	800	300	424	36
56														9	NO	800	500	500	600		1, 3, 5, 7	NO	800	500	707	600
57														9	NO	1000	75	67	90		1, 3, 5, 7	NO	1000	75	103	90
58														9	NO	1000	150	134	180		1, 3, 5, 7	NO	1000	150	206	180
59														9	NO	1000	300	268	36		1, 3, 5, 7	NO	1000	300	412	36
60														9	NO	1000	500	447	600		1, 3, 5, 7	NO	1000	500	687	600
61														9	NO	1200	75	65	90		1, 3, 5, 7	NO	1200	75	96	90
62														9	NO	1200	150	130	180		1, 3, 5, 7	NO	1200	150	192	180
63														9	NO	1200	300	260	36		1, 3, 5, 7	NO	1200	300	384	36
64														9	NO	1200	500	433	600		1, 3, 5, 7	NO	1200	500	640	600
65														9	NO	1400	75	63	600		1, 3, 5, 7	NO	1400	75	94	600
66														9	NO	1400	150	126	90		1, 3, 5, 7	NO	1400	150	188	90
67														9	NO	1400	300	252	180		1, 3, 5, 7	NO	1400	300	376	180
68														9	NO	1400	500	420	36		1, 3, 5, 7	NO	1400	500	627	36
69														9	NO	1600	75	56	600		1, 3, 5, 7	NO	1600	75	87	600
70														9	NO	1600	150	112	90		1, 3, 5, 7	NO	1600	150	174	90
71														9	NO	1600	300	224	180		1, 3, 5, 7	NO	1600	300	348	180
72														9	NO	1600	500	373	36		1, 3, 5, 7	NO	1600	500	580	36
73														9	NO	1800	75	50	600		1, 3, 5, 7	NO	1800	75	86	600
74														9	NO	1800	150	100	90		1, 3, 5, 7	NO	1800	150	172	90
75														9	NO	1800	300	200	180		1, 3, 5, 7	NO	1800	300	344	180
76														9	NO	1800	500	333	36		1, 3, 5, 7	NO	1800	500	573	36
77														9	NO	2000	75	49	600		1, 3, 5, 7	NO	2000	75	84	600
78														9	NO	2000	150	98	90		1, 3, 5, 7	NO	2000	150	168	90
79														9	NO	2000	300	196	180		1, 3, 5, 7	NO	2000	300	336	180
80														9	NO	2000	500	327	36		1, 3, 5, 7	NO	2000	500	560	36
81														9	NO	2200	75	48	600		1, 3, 5, 7	NO	2200	75	83	600
82														9	NO	2200	150	96	90		1, 3, 5, 7	NO	2200	150	166	90
83														9	NO	2200	300	192	180		1, 3, 5, 7	NO	2200	300	332	180
84														9	NO	2200	500	320	36		1, 3, 5, 7	NO	2200	500	553	36
85														9	NO	2400	75	42	600		1, 3, 5, 7	NO	2400	75	82	600
86														9	NO	2400	150	84	90		1, 3, 5, 7	NO	2400	150	164	90
87														9	NO	2400	300	168	180		1, 3, 5, 7	NO	2400	300	328	180
88														9	NO	2400	500	280	36		1, 3, 5, 7	NO	2400	500	547	36
89														9	NO	2600	75	40	600		1, 3, 5, 7	NO	2600	75	81	600
90														9	NO	2600	150	80	90		1, 3, 5, 7	NO	2600	150	162	90
91														9	NO	2600	300	160	180		1, 3, 5, 7	NO	2600	300	324	180
92														9	NO	2600	500	267	36		1, 3, 5, 7	NO	2600	500	540	36
Sheet6

Cell Formulas
Range	Formula
D5	D5	=INDEX(FILTER($X$5:$X$92,($V$5:$V$92=B5)*($W$5:$W$92=C5)*($Y$5:$Y$92>=F5)),1)
E5	E5	=INDEX($Y$5:$Y$92,MATCH(1,($W$5:$W$101=C5)*($V$5:$V$92=B5)*($X$5:$X$101=D5),0))
H5	H5	=SUM(D5)*1.2/E5
I5	I5	=SUM(H5)*F5
J5	J5	=SUM(I5+G5)
K5	K5	=IF(J5>SUMPRODUCT((Z5:Z92)*(C5=W5:W92)*(D5=X5:X92)*(E5=Y5:Y92)),INDEX(X5:X92,1+SUM((C5=W5:W92)*(D5=X5:X92)*(E5=Y5:Y92)*SEQUENCE(88))),D5)

 

[bfuentes1412]

I'm a bit lost on your formula, happy to leave you in the more capable hands of @Peter_SSs .

No problem. Appreciate your help thus far!

 

[Peter_SSs]

I'm a bit lost on your formula, happy to leave you in the more capable hands of @Peter_SSs .

I am also not on top of how the K5 formula should work. The descriptions do not really mean anything to me so I do not know exactly how the formula should work.

 

[bfuentes1412]

@Peter_SSs Would you like me to explain it further or would providing scenarios/examples be more helpful?

 

[Peter_SSs]

@Peter_SSs Would you like me to explain it further or would providing scenarios/examples be more helpful?

Either or both may help.

Does your existing K5 formula work for table 2? If so, then as a starting point you could try something like this.

=IF(A5<9,existing K5 formula, existing K5 formula modified to refer to table 1)