How to find the Value form the table comparing

[ajmal pottekattil yoousuf]

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

[#2]

@ajmal pottekattil yoousuf 

Which exactly columns you'd like to compare and what shall be as result?

[#3]

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.

[#4]

@ajmal pottekattil yoousuf 

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?

[#5 · Accepted solution]

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