Forum Discussion

ajmal_pottekattil_yoousuf's avatar
Apr 19, 2024

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

  • ajmal_pottekattil_yoousuf 

    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"
        )
    ));
    • ajmal_pottekattil_yoousuf's avatar
      ajmal_pottekattil_yoousuf
      Iron Contributor
      3rd 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.
  • ajmal_pottekattil_yoousuf 

    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"
        )
    ));

Resources