Forum Discussion
Higher-End Excel Formula Help
Patrick2788 I typed that formula into B2 and I got a return value of 1, is there something else I need? I double checked that my formula was correct. I also tried editing the number value to 7 to include all of "3Ø, 42P"
And the reason why I've tried to do it this way is because Column D "LOAD DESCRIPTION" is the next piece of information I want to have referenced into my PANEL SCHEDULES templates based off the PANEL#, Ø & POLE, PANEL NAME and CIRCUIT#. If I could figure out how to get the PANEL NAME to show, it should be the same logic to populate the LOAD DESCRIPTION as well
I've attached my solution. If this isn't perfect it may at least give you some ideas.
- Yes, you can also use LOOKUP in your other Panel Schedules.
Twifoo I didn't think a normal LOOKUP would work until your last response. Thank you for showing me an additional way. Followup question - can this same logic be used across the different Panel Schedule templates that I have? That may be a "dumb question", but please bear with me if it is a trivial response
No Ctrl+Shift+Enter is required for this succinct non-array formula in C10 of the attached version of your file:
=IFERROR(LOOKUP(2,1/(
'PANEL INPUT'!$A$29:$A$500&'PANEL INPUT'!$B$29:$B$500&'PANEL INPUT'!$C$29:$C$500&'PANEL INPUT'!$F$29:$F$500=1&$B$2&$D$5&B10),
'PANEL INPUT'!$D$29:$D$500),"")Moreover, no "V", as in victory, would be achieved in using VLOOKUP. The old but reliable LOOKUP would suffice and you don't have to CHOOSE, as you can see in the result of the foregoing formula, which is shown below:
You're welcome. Enjoy your day!
Patrick2788 This worked out very well - thank you! Additionally, I figured out why I was getting a blank cell as a return before. I was not doing a "CTRL SHIFT ENTER" after editing the formula from $D$29:$D$500 to $E$29:$E$500. Both ways work exactly the same for the VA value.
Patrick, thank you for all your time and effort for assisting me in this complex manner!! Very much appreciated
I'm sorry to inform you that you didn't answer the question I interposed earlier. Nonetheless, I presume that E29 in PANEL INPUT Sheet with the value of 1000 is the cell wherein you want to create a formula. If my presumption is correct, my next question would be:
How did you arrive at that value of 1000?
Since you're pulling back a number, you can use SUMIFS:
=SUMIFS('PANEL INPUT'!$E$29:$E$100,'PANEL INPUT'!$A$29:$A$100,1,'PANEL INPUT'!$F$29:$F$100,'3Ø PANEL SCHEDULES'!B10,'PANEL INPUT'!$B$29:$B$100,'3Ø PANEL SCHEDULES'!$B$2,'PANEL INPUT'!$D$29:$D$100,'3Ø PANEL SCHEDULES'!C10)Patrick2788 Twifoo See attached for latest spreadsheet.
Patrick has been able to help me populate the LOAD DESCRIPTION and PANEL NAME under the 3Ø PANEL SCHEDULES tab, and the 3Ø, 42P template (first one). I was trying to use the same "formula structure" for the LOAD DESCRIPTION (IFERROR) to read in the value for VA. The VA value is pulled from the same input table, under PANEL INPUT tab called LOAD (VA), or column E. Originally, I left the LOAD (VA) column blank, as I wanted to get the structure for LOAD DESCRIPTION down first under PANEL INPUT to read into my templates. Now, I wish to do the same thing for LOAD (VA) and have that read into my panel schedules with the same logic (same PANEL #, Ø & POLE, PANEL NAME and CIRCUIT # as parameters). I was trying to understand the logic all the way through LOAD DESCRIPTION, so I could modify it slightly to read in the LOAD (VA) instead under column K on the 3Ø PANEL SCHEDULES tab (ultimately K10 for this instance).
Sorry if that was too wordy with all the references, but I hope that clears up the questions ^
Thank you all again
Is there something for VLOOKUP to return for that one? It could be bringing back an empty (which should usually show as 0).
Patrick2788 I think I get that. If I wanted to use the same "structured formula" for the VA, why can't I switch the value_2 from D29-D500 to E29-E500? I get a blank cell when I do so
{1,2} indicates two vertical arrays. Think of how CSV delimits columns - it uses commas. The same for arrays. The {1,2} is our way of saying the values used in CHOOSE will be arrays and not single values.
Patrick2788 I see. Can you explain what the {1,2} does then? That is the last part I'm not 100% clear on. After doing a little research, I see that the index_num argument is a value you choose but not sure how that ties in with this context
CHOOSE allows us to concatenate the columns in the table array -- the goal is to create a unique list of items to pick from. The other approach would be to use an array to arrive at the correct row number based on multiple criteria. The array would then be used in an INDEX. CHOOSE is a bit more cleaner.
Patrick2788 Thank you for providing your response. I want to make sure I understand the logic completely for the Panel Name and Load Description, as I did not think of IFERROR at all
Panel Name: Simple HLOOKUP. Looks for "3Ø, 42P", Searches my table (including bolded headers) on PANEL INPUT, searches row 2 (first row of PANEL NAMES) since it is PANEL #1 of my templates, and returns the match. I think should work for my other templates, with the 2 increasing by n+1 for each panel
Load Description: So with IFERROR, it will "catch" the error and provide an alternative solution. That solution is a VLOOKUP. In the DP12 example, the lookup is for the following: "1" for Panel #1 (locked value), B2 for Phase and Pole (locked value), D5 for Panel Name (locked value) and B11 for the circuit number (unlocked value, changes based off load description cell). For the tables to retrieve the value from, this is where I have a question. I haven't used the CHOOSE function before, can you elaborate on it some if you wouldn't mind? I get the PANEL INPUT range values after that, as that is the table I want to choose the values from.
Thank you again for your help
