Forum Discussion
Using Index and Match
I’m trying to create a formula in which you enter the kW and A of a motor and it will return the correct drive to use for that motor. Based on my understanding, the current formula I am using s...
I had fun with this using 365. Firstly, 'Data from Picture' worked OK!
I arranged the data so that the power and current requirements could be treated as arrays and the two criteria compared simultaneously. To get a list of acceptable products one may have
= LET(
specSatisfied?, BYROW(spec>required, LAMBDA(x, AND(x))),
FILTER(Table1, specSatisfied?)
)
(as in reply above). To select the first occurrence
= LET(
specSatisfied?, BYROW(spec>required, LAMBDA(x, AND(x))),
XLOOKUP(TRUE, specSatisfied?, Table1)
)
The chosen formula should, most likely, be wrapped in an outer Lambda function to prevent formulas appearing on the worksheet!
ps Not all would agree!
To continue a step further, my worksheet formulas are now reduced to
= GEXLookupλ(Table1, specification, required)
or
= FilterGEλ(Table1, specification, required)The Named λ-functions are now
GEXLookupλ
= LET(
specSatisfied?, BYROW(spec >= req, ANDλ),
XLOOKUP(TRUE, specSatisfied?, table)
)
FilterGEλ
= LET(
specSatisfied?, BYROW(spec >= req, ANDλ),
FILTER(table, specSatisfied?)
)
ANDλ
= AND(x)