Forum Discussion

Jamesrr13's avatar
Jamesrr13
Copper Contributor
Aug 06, 2023

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

  • Jamesrr13 

    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!

     

    • PeterBartholomew1's avatar
      PeterBartholomew1
      Silver 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)

       

  • Jamesrr13 

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

     

  • SnowMan55's avatar
    SnowMan55
    Bronze Contributor

    Jamesrr13 

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

     

Resources