Forum Discussion
Help with cross referencing using XMATCH and INDEX.
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_ThompsonAug 24, 2023Copper ContributorVery interesting approach, I've never seen lambda functions in Excel. I'm assuming they're similar to Python's lambda functions?
- PeterBartholomew1Aug 24, 2023Silver Contributor
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).