Forum Discussion
Lookup Value from the range and convert into another unit
Lookup value From the value from the range table
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
could you please refer the excel file.
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)
I use Excel 2019 version.
Sorry, can't help when, perhaps someone else
