SOLVED

Iron 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

4 Replies

# Re: How to find the Value form the table comparing

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

# Re: How to find the Value form the table comparing

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.

# Re: How to find the Value form the table comparing

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?

best response confirmed by ajmal pottekattil yoousuf (Iron Contributor)
Solution

# Re: How to find the Value form the table comparing

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"
)
));``````
1 best response

Accepted Solutions
best response confirmed by ajmal pottekattil yoousuf (Iron Contributor)
Solution

# Re: How to find the Value form the table comparing

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