I´m using a formula to achieve the desired result & want to convert it to dynamic array. I tried using MAP function with LAMBDA, but it is failing. Can the experts guide please?
Rules -
Compare source table with another table. If a dept is marked 'x' in the source table & if it exists in the 2nd table, mark 'x' in the output. If not, mark, mark 'no'. If source table is not marked 'x', enter 'NA' in the output.
I´m getting the desired output when I copy & paste the formula. But, when I tried to convert it to dynamic array, it is throwing error.
Source table:
Compare table:
Desired output:
Dynamic array:
Rules -
Compare source table with another table. If a dept is marked 'x' in the source table & if it exists in the 2nd table, mark 'x' in the output. If not, mark, mark 'no'. If source table is not marked 'x', enter 'NA' in the output.
I´m getting the desired output when I copy & paste the formula. But, when I tried to convert it to dynamic array, it is throwing error.
Source table:
excel problems.xlsx | ||||||
---|---|---|---|---|---|---|
B | C | D | E | |||
25 | name | sales | hr | |||
26 | ffa | ffa@123.com | x | |||
27 | jkkj | jkkj@123.com | x | |||
28 | sasa | sasa@123.com | x | x | ||
29 | gfgf | gfgf@123.com | x | |||
30 | trtr1 | trtr1@123.com | x | |||
31 | oioi | oioi@123.com | x | |||
32 | nmn | nmn@123.com | x | |||
33 | cxcxb | cxcxb@123.com | ||||
34 | nhnh | nhnh@123.com | ||||
35 | jooj | jooj@123.com | x | x | ||
merge tables |
Compare table:
excel problems.xlsx | ||||
---|---|---|---|---|
G | H | |||
25 | dept | |||
26 | sales | ffa@123.com | ||
27 | sales | sasa@123.com | ||
28 | sales | trtr1@123.com | ||
29 | hr | jkkj@123.com | ||
30 | hr | sasa@123.com | ||
merge tables |
Desired output:
excel problems.xlsx | ||||||||
---|---|---|---|---|---|---|---|---|
J | K | L | M | N | O | |||
25 | name | sales | hr | comp-sales | comp-hr | |||
26 | ffa | ffa@123.com | x | yes | NA | |||
27 | jkkj | jkkj@123.com | x | NA | yes | |||
28 | sasa | sasa@123.com | x | x | yes | yes | ||
29 | gfgf | gfgf@123.com | x | no | NA | |||
30 | trtr1 | trtr1@123.com | x | yes | NA | |||
31 | oioi | oioi@123.com | x | NA | no | |||
32 | nmn | nmn@123.com | x | no | NA | |||
33 | cxcxb | cxcxb@123.com | NA | NA | ||||
34 | nhnh | nhnh@123.com | NA | NA | ||||
35 | jooj | jooj@123.com | x | x | no | no | ||
merge tables |
Cell Formulas | ||
---|---|---|
Range | Formula | |
N26:N35 | N26 | =LET( sEmail, tSource[email], cEmail, tComp[email], cDept, tComp[dept], cEmailSales, FILTER(tComp, cDept="sales"), cEmailHr, FILTER(tComp, cDept="hr"), a, IF(D26="x",IFNA(IF(LEN(XLOOKUP(C26,CHOOSECOLS(cEmailSales,2), CHOOSECOLS(cEmailSales,2)))>0,"yes"),"no"),"NA"), a) |
O26:O35 | O26 | =LET( sEmail, tSource[email], cEmail, tComp[email], cDept, tComp[dept], cEmailSales, FILTER(tComp, cDept="sales"), cEmailHr, FILTER(tComp, cDept="hr"), a, IF(E26="x",IFNA(IF(LEN(XLOOKUP(C26,CHOOSECOLS(cEmailHr,2), CHOOSECOLS(cEmailHr,2)))>0,"yes"),"no"),"NA"), a) |
Dynamic array:
excel problems.xlsx | |||
---|---|---|---|
R | |||
26 | #CALC! | ||
merge tables |
Cell Formulas | ||
---|---|---|
Range | Formula | |
R26 | R26 | =LET( sEmail, tSource[email], cEmail, tComp[email], cDept, tComp[dept], cEmailSales, FILTER(tComp, cDept="sales"), cEmailHr, FILTER(tComp, cDept="hr"), a, MAP(tSource, LAMBDA(src, IF(CHOOSECOLS(tSource,2)="x",IFNA(IF(LEN(XLOOKUP(CHOOSECOLS(sEmail,2),CHOOSECOLS(cEmailSales,2), CHOOSECOLS(cEmailSales,2)))>0,"yes"),"no"),"NA"))), a) |