Forum Discussion
Higher-End Excel Formula Help
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
{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.
- 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
