Forum Discussion
ajmal_pottekattil_yoousuf
Dec 17, 2018Iron Contributor
Lookup Value from the range and convert into another unit
Lookup value From the value from the range table
- 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
ajmal_pottekattil_yoousuf
Iron Contributor
How to find the CMC1 and CMC2 based on the AC Voltage and Frequency.
I attached the video for reference.
ajmal_pottekattil_yoousuf
Feb 23, 2023Iron Contributor
Could you please guide me.
- SergeiBaklanMar 01, 2023MVP
If more close to Excel file, could you please specify what shall be calculated in which cell of the spreadsheet.
- ajmal_pottekattil_yoousufMar 04, 2023Iron Contributor
could you please refer the excel file.
- SergeiBaklanMar 09, 2023MVP
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)