Addition to INDEX formula to choose which table to reference

bfuentes1412

New Member
Joined
Apr 21, 2010
Messages
37
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJKLMNOPQRSTUVWXYZ
4ZONEEVAdjust Design ft(2)XFMR SIZE Total # of Allowable Cust.# of Customers Additional LoadKVA PER CUSTMAX DWELLING UTILIZATIONTOTAL LOADRecommended Xfrm SizeZONEEVsqftxfmrcust #REMOVAL POINTZONEEVsqftxfmrcust #REMOVAL POINT
53NO2600758147101.1152.2262.22759YES6007521901, 3, 5, 7YES600752390
69YES600150421801, 3, 5, 7YES60015046180
79YES60030084361, 3, 5, 7YES6003009236
89YES6005001406001, 3, 5, 7YES600500153600
99YES8007520901, 3, 5, 7YES800752290
109YES800150401801, 3, 5, 7YES80015044180
119YES80030080361, 3, 5, 7YES8003008836
129YES8005001336001, 3, 5, 7YES800500147600
139YES10007520901, 3, 5, 7YES1000752290
149YES1000150401801, 3, 5, 7YES100015044180
159YES100030080361, 3, 5, 7YES10003008836
169YES10005001336001, 3, 5, 7YES1000500147600
179YES12007520901, 3, 5, 7YES1200752190
189YES1200150401801, 3, 5, 7YES120015042180
199YES120030080361, 3, 5, 7YES12003008436
209YES12005001336001, 3, 5, 7YES1200500140600
219YES14007519901, 3, 5, 7YES1400752190
229YES1400150381801, 3, 5, 7YES140015042180
239YES140030076361, 3, 5, 7YES14003008436
249YES14005001276001, 3, 5, 7YES1400500140600
259YES16007519901, 3, 5, 7YES1600752190
269YES1600150381801, 3, 5, 7YES160015042180
279YES160030076361, 3, 5, 7YES16003008436
289YES16005001276001, 3, 5, 7YES1600500140600
299YES18007519901, 3, 5, 7YES1800752090
309YES1800150381801, 3, 5, 7YES180015040180
319YES180030076361, 3, 5, 7YES18003008036
329YES18005001276001, 3, 5, 7YES1800500133600
339YES20007518901, 3, 5, 7YES2000752090
349YES2000150361801, 3, 5, 7YES200015040180
359YES200030072361, 3, 5, 7YES20003008036
369YES20005001206001, 3, 5, 7YES2000500133600
379YES22007518901, 3, 5, 7YES2200751990
389YES2200150361801, 3, 5, 7YES220015038180
399YES220030072361, 3, 5, 7YES22003007636
409YES22005001206001, 3, 5, 7YES2200500127600
419YES24007517901, 3, 5, 7YES2400751990
429YES2400150341801, 3, 5, 7YES240015038180
439YES240030068361, 3, 5, 7YES24003007636
449YES24005001136001, 3, 5, 7YES2400500127600
459YES26007515901, 3, 5, 7YES2600751990
469YES2600150301801, 3, 5, 7YES260015038180
479YES260030060361, 3, 5, 7YES26003007636
489YES26005001006001, 3, 5, 7YES2600500127600
499NO60075103901, 3, 5, 7NO6007512990
509NO6001502061801, 3, 5, 7NO600150258180
519NO600300412361, 3, 5, 7NO60030051636
529NO6005006876001, 3, 5, 7NO600500860600
539NO8007575901, 3, 5, 7NO8007510690
549NO8001501501801, 3, 5, 7NO800150212180
559NO800300300361, 3, 5, 7NO80030042436
569NO8005005006001, 3, 5, 7NO800500707600
579NO10007567901, 3, 5, 7NO10007510390
589NO10001501341801, 3, 5, 7NO1000150206180
599NO1000300268361, 3, 5, 7NO100030041236
609NO10005004476001, 3, 5, 7NO1000500687600
619NO12007565901, 3, 5, 7NO1200759690
629NO12001501301801, 3, 5, 7NO1200150192180
639NO1200300260361, 3, 5, 7NO120030038436
649NO12005004336001, 3, 5, 7NO1200500640600
659NO140075636001, 3, 5, 7NO14007594600
669NO1400150126901, 3, 5, 7NO140015018890
679NO14003002521801, 3, 5, 7NO1400300376180
689NO1400500420361, 3, 5, 7NO140050062736
699NO160075566001, 3, 5, 7NO16007587600
709NO1600150112901, 3, 5, 7NO160015017490
719NO16003002241801, 3, 5, 7NO1600300348180
729NO1600500373361, 3, 5, 7NO160050058036
739NO180075506001, 3, 5, 7NO18007586600
749NO1800150100901, 3, 5, 7NO180015017290
759NO18003002001801, 3, 5, 7NO1800300344180
769NO1800500333361, 3, 5, 7NO180050057336
779NO200075496001, 3, 5, 7NO20007584600
789NO200015098901, 3, 5, 7NO200015016890
799NO20003001961801, 3, 5, 7NO2000300336180
809NO2000500327361, 3, 5, 7NO200050056036
819NO220075486001, 3, 5, 7NO22007583600
829NO220015096901, 3, 5, 7NO220015016690
839NO22003001921801, 3, 5, 7NO2200300332180
849NO2200500320361, 3, 5, 7NO220050055336
859NO240075426001, 3, 5, 7NO24007582600
869NO240015084901, 3, 5, 7NO240015016490
879NO24003001681801, 3, 5, 7NO2400300328180
889NO2400500280361, 3, 5, 7NO240050054736
899NO260075406001, 3, 5, 7NO26007581600
909NO260015080901, 3, 5, 7NO260015016290
919NO26003001601801, 3, 5, 7NO2600300324180
929NO2600500267361, 3, 5, 7NO260050054036
Sheet6
Cell Formulas
RangeFormula
D5D5=INDEX(FILTER($X$5:$X$92,($V$5:$V$92=B5)*($W$5:$W$92=C5)*($Y$5:$Y$92>=F5)),1)
E5E5=INDEX($Y$5:$Y$92,MATCH(1,($W$5:$W$101=C5)*($V$5:$V$92=B5)*($X$5:$X$101=D5),0))
H5H5=SUM(D5)*1.2/E5
I5I5=SUM(H5)*F5
J5J5=SUM(I5+G5)
K5K5=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)
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
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.
 
Upvote 0
@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)
 
Upvote 0

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top