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 should return the first drive however it’s returning the last one. How can I fix this?
EDIT: Both the kW and the A entered must be less than the corresponding values in the table for the drive to be usable.
4 Replies
- PeterBartholomew1Silver Contributor
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!
- PeterBartholomew1Silver Contributor
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)
- OliverScheurichGold Contributor
=INDEX($B$32:$B$54,MATCH(1,(I31<$C$32:$C$54)*(I32<$D$32:$D$54),0))
An alternative could be this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.
- SnowMan55Bronze Contributor
If you are using Excel 365/Excel for the web, and as your "table" is sorted, try:
=TAKE( FILTER(B$32:B$54, (C$32:C$54>$I$31)*(D$32:D$54>$I$32), "none"), 1, 1)
(FILTER to get just motors that support the specified load, then TAKE just the first of those.)