Forum Discussion

Kyle_Thompson's avatar
Kyle_Thompson
Copper Contributor
Aug 22, 2023

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 whi...
Excel
Formulas and Functions
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Aug 23, 2023

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?

  • Kyle_Thompson's avatar
    Kyle_Thompson
    Copper Contributor
    Aug 24, 2023
    Very interesting approach, I've never seen lambda functions in Excel. I'm assuming they're similar to Python's lambda functions?
    • PeterBartholomew1's avatar
      PeterBartholomew1
      Silver Contributor
      Aug 24, 2023

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

Resources