Forum Discussion
Help with cross referencing using XMATCH and INDEX.
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?
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?
- Very interesting approach, I've never seen lambda functions in Excel. I'm assuming they're similar to Python's lambda functions?
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).
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):
=INDEX('THHN_THWN_THWN-2'!$C$4:$N$27,XMATCH(Test!$A3,'THHN_THWN_THWN-2'!$B$4:$B$27),)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)- Thank you! I appreciate the advice!
=INDEX($B$2:$G$2,SMALL(IF(OFFSET($B$3:$G$3,MATCH(C14,$A$4:$A$10,0),0)>C15,COLUMN($A$1:$F$1)),1))You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.
