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