Help with cross referencing using XMATCH and INDEX.

[Kyle_Thompson]

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?

[#2]

@Kyle_Thompson 

=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.

[#3]

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)

[#4]

@Kyle_Thompson 

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?

[#5]

Thank you! I appreciate the advice!

[#6]

Very interesting approach, I've never seen lambda functions in Excel. I'm assuming they're similar to Python's lambda functions?

[#7]

@Kyle_Thompson 

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).