Forum Discussion
ajmal_pottekattil_yoousuf
Apr 19, 2024Iron Contributor
How to find the Value form the table comparing
I want to find the cmc-1
For finding the cmc-1 , First find the match function .But i don't know how to use the match function by comparing the two columns.
Please refer the excel file i am attached
In this case how to use the match function
Ho
In general you may combine calculations with LET/LAMBDA without helper columns
using
coeff = LAMBDA(unit, par, LET( range, IF(par="V", VoltageUnits, FrequencyUnits), XLOOKUP( unit, CHOOSECOLS(range, 1), CHOOSECOLS( range, 2) ) ) ); cmcOne=LAMBDA(input, LET( v, INDEX(input,1,1)*coeff(INDEX(input,1,2), "V"), Hz, INDEX(input,1,3)*coeff(INDEX(input,1,4), "Hz"), VolFrom, CHOOSECOLS(data,1)*coeff(CHOOSECOLS(data,2), "V"), VolTo, CHOOSECOLS(data,3)*coeff(CHOOSECOLS(data,4), "V"), FreqFrom,CHOOSECOLS(data,5)*coeff(CHOOSECOLS(data,6),"Hz"), FreqTo, CHOOSECOLS(data,7)*coeff(CHOOSECOLS(data,8),"Hz"), IFERROR( TOCOL( CHOOSECOLS(data,9)/ ( (v >= VolFrom) * (v<=VolTo) * (Hz >= FreqFrom) * (Hz <= FreqTo) ), 3), "no such" ) ));
Which exactly columns you'd like to compare and what shall be as result?
- ajmal_pottekattil_yoousufIron Contributor3rd column and 6 th column
compare this column and return the row number.
After getting the row number the find the value from the CMC-1 from the main table.If to use generated columns that could be
=XLOOKUP(1, N14#*Q14#, AA3:AA33 )
for CMC-1. Or you need row number as well for some other purposes?
In general you may combine calculations with LET/LAMBDA without helper columns
using
coeff = LAMBDA(unit, par, LET( range, IF(par="V", VoltageUnits, FrequencyUnits), XLOOKUP( unit, CHOOSECOLS(range, 1), CHOOSECOLS( range, 2) ) ) ); cmcOne=LAMBDA(input, LET( v, INDEX(input,1,1)*coeff(INDEX(input,1,2), "V"), Hz, INDEX(input,1,3)*coeff(INDEX(input,1,4), "Hz"), VolFrom, CHOOSECOLS(data,1)*coeff(CHOOSECOLS(data,2), "V"), VolTo, CHOOSECOLS(data,3)*coeff(CHOOSECOLS(data,4), "V"), FreqFrom,CHOOSECOLS(data,5)*coeff(CHOOSECOLS(data,6),"Hz"), FreqTo, CHOOSECOLS(data,7)*coeff(CHOOSECOLS(data,8),"Hz"), IFERROR( TOCOL( CHOOSECOLS(data,9)/ ( (v >= VolFrom) * (v<=VolTo) * (Hz >= FreqFrom) * (Hz <= FreqTo) ), 3), "no such" ) ));