Forum Discussion

ajmal_pottekattil_yoousuf's avatar
ajmal_pottekattil_yoousuf
Iron Contributor
Dec 17, 2018

Lookup Value from the range and convert into another unit

Lookup value From the value from the range table   
Excel
  • SergeiBaklan's avatar
    SergeiBaklan
    Dec 17, 2018

    That could be like

    =INDEX(Sheet0!$F$111:$F$120,
     MATCH(1,INDEX(
        ($B7*(($C7="µA")*0.000001+($C7="mA")*0.001+($C7="A"))>=Sheet0!$B$111:$B$120*((Sheet0!$C$111:$C$120="µA")*0.000001+(Sheet0!$C$111:$C$120="mA")*0.001+(Sheet0!$C$111:$C$120="A")))*
       ($B7*(($C7="µA")*0.000001+($C7="mA")*0.001+($C7="A"))<=Sheet0!$D$111:$D$120*((Sheet0!$E$111:$E$120="µA")*0.000001+(Sheet0!$E$111:$E$120="mA")*0.001+(Sheet0!$E$111:$E$120="A"))),
0,),))

    but much better to convert ranges into excel tables and add helper columns to adjust units. Please see attached

     

SergeiBaklan's avatar
SergeiBaklan
MVP
Mar 01, 2023

ajmal_pottekattil_yoousuf 

If more close to Excel file, could you please specify what shall be calculated in which cell of the spreadsheet.

  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Mar 09, 2023

    ajmal_pottekattil_yoousuf 

    Thank you, but the formal logic why it shall be selected this or that value is still unclear. Based on assumptions and assuming you are on 365 for the first case it could be

    =LET(
  f,  FILTER(Sheet0!$B$4:$L$38,(Sheet0!$E$4:$E$38=$A32) * (Sheet0!$G$4:$G$38=C$32)  ),
  ff, FILTER(f,CHOOSECOLS(f,3)>=$B32),
  xx, XLOOKUP( $D32, CHOOSECOLS(ff,5), CHOOSECOLS(ff, 9),,-1 ),
  xx  )

    (see M32:M34 in attached)

Resources