Aug 22 2023 04:34 PM
Aug 22 2023 04:34 PM
The task I'm trying to accomplish is to create an automatic lookup method for conduit sizing. I'm referencing the tables in Annex C of the 2023 NEC, and I've copied one portion into a spreadsheet which I'd like to reference with a function.
The goal is to find the minimum conduit size for a given number of conductors of a given diameter. So, for example, if I had 20 #12 conductors, I would first find the #12 row, then read right until I found a number larger than 20, then reference the column header to find that I need a 1" conduit.
From what I've seen in the documentation, I can use XMATCH, INDEX, or maybe XLOOKUP to solve this, but I'm having trouble figuring out how to combine them.
=XMATCH(Test!A3,'THHN_THWN_THWN-2'!$B$4:$B$27): returns the correct row number for the conductor size I'm looking for.
=INDEX('THHN_THWN_THWN-2'!$C$4:$N$27,XMATCH(Test!$A3,'THHN_THWN_THWN-2'!$B$4:$B$27),1): returns the first column of the correct row.
I feel like I'm one step away, but I can't figure out how to search the row for the column I'm looking for. Any thoughts?
Aug 22 2023 05:35 PM
You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.
Aug 22 2023 07:53 PM - edited Aug 22 2023 08:01 PM
You're right you are close. Leaving the column/row blank in INDEX (or giving it a 0) will return the whole row/column so in this case leaving the column blank (but you must still have the comma to indicate the column parameter):
should return the whole row so then wrap it with XLOOKUP:
=XLOOKUP(Test!$B3, INDEX('THHN_THWN_THWN-2'!$C$4:$N$27,XMATCH(Test!$A3,'THHN_THWN_THWN-2'!$B$4:$B$27),), 'THHN_THWN_THWN-2'!$C$2:$N$2, "not found", 1)
But if you use this in multiple locations (or even not) I recommend you NAME those ranges. for example:
ConduitRng = 'THHN_THWN_THWN-2'!$C$2:$N$2
ConductorRng = 'THHN_THWN_THWN-2'!$B$4:$B$27
CountRng = 'THHN_THWN_THWN-2'!$C$4:$N$27
so then the formula READS like:
=XLOOKUP($B3, INDEX(CountRng, XMATCH($A3, ConductorRng),), ConduitRng, "not found", 1)
BTW since you used A3 as the conductor I am assuming B3 for the count.
and lastly you should be able to make an array output accordingly:
=XLOOKUP($B3:$B100, INDEX(CountRng, XMATCH($A3:$A100, ConductorRng),), ConduitRng, "not found", 1)
Aug 23 2023 01:41 AM - edited Aug 23 2023 01:42 AM
I am all in favour of @mtarler 's use of names. I would probably use two applications of XLOOKUP but there is very little to choose between the formulas. Dressing it up a bit further with LET one gets
= LET( conductorCountRng, XLOOKUP(conductorSize, conductorSizeRng, conductorCountArr), conduitSize, XLOOKUP(conductorCount, conductorCountRng, conduitSize, , 1), conduitSize )
Of course, it is possible to take things a step further and introduce a Lambda function, ConduitSizeλ, which refers to the formula above.
ConduitSizeλ = LAMBDA(size, count, LET( conductorCountRng, XLOOKUP(size, conductorSizeRng, conductorCountArr), conduitSize, XLOOKUP(count, conductorCountRng, conduitSize, , 1), conduitSize ) )
giving the worksheet formula
= ConduitSizeλ(conductorSize, conductorCount)
Many spreadsheet users would hate this because it hides the working. On the other hand, what is the benefit to the end user of seeing the working?
Aug 24 2023 08:35 AM
Aug 24 2023 08:58 AM
Yes, the new capabilities in Excel 365 share a common functional programming heritage with Python, including the use of Lambda helper functions such as MAP and SCAN. In recent years, I have restricted my programming approach to array formulas (treating an relative references as errors) and use defined names (or Tables) for referencing all data (no A1-type references).