英文:
Find the Rank 01 Matching element against the Given Key word
问题
I have two tables, Table two with code and Key words.
Table 01
Table 01 With combination of Key words, SO need to find the height matching row of each description and need to give the rank one matching code as code against the Description.
For Easy reference I added video in LOOM, please refer below video.
I created G- Sheet as well, If it is easy, please provide the solution on that.
Best regard
Indika
英文:
I have two tables, Table two with code and Key words.
Table 01
Table 01 With combination of Key words, SO need to find the height matching row of each description and need to give the rank one matching code as code against the Description.
For Easy reference I added video in LOOM, please refer below video.
https://www.loom.com/share/fbabeb8f190b44c7b88e82f90770c8eb?sid=5852e721-c471-4fbf-a0dd-191235e4a2fa
I created G- Sheet as well, If it is easy, please provide the solution on that.
https://docs.google.com/spreadsheets/d/1OvxT1gpkUncEvPxLaf_ob6qqwijciEQ0AjfPn7qJyT0/edit?usp=sharing
Best regard
Indika
答案1
得分: 2
以下是您提供的内容的中文翻译：
如果我理解正确，那么这应该有效。假设没有Excel约束，也就是说根据您的标签，我们假设您在使用MS365
• 单元格B2中使用的公式
=LET(
a,HSTACK($D$2:$D$8,
BYROW(SIGN(IFERROR(XMATCH($E$2:$K$8,TEXTSPLIT(A2," ")),0)),LAMBDA(x,SUM(x)))),
@SORT(a,2,-1))
或者，使用<kbd>MAP()</kbd>
• 单元格B2中使用的公式
=MAP(A2:A6,LAMBDA(a,
LET(b,TEXTSPLIT(a," "),
d,BYROW(SIGN(IFERROR(XMATCH(E2:K8,b),0)),LAMBDA(c,SUM(c))),
e,HSTACK(D2:D8,d),@SORT(e,2,-1))))
另一种不使用<kbd>LAMBDA()</kbd>的替代方法
• 单元格B2中使用的公式
=LET(
a,N(IFERROR(XMATCH($E$2:$K$8,TEXTSPLIT(A2," ")),0)<>0),
b,HSTACK($D$2:$D$8,MMULT(a,SEQUENCE(ROWS(a),,,0))),
@SORT(b,2,-1))
如果您想要一个单一的数组公式。
=MAP(A2:A6,LAMBDA(m,LET(
a,N(IFERROR(XMATCH($E$2:$K$8,TEXTSPLIT(m," ")),0)<>0),
b,HSTACK($D$2:$D$8,MMULT(a,SEQUENCE(ROWS(a),,,0))),
@SORT(b,2,-1))))
英文:
If I have understood correctly, then this would work. Assuming there is no Excel Constraints, i.e. we assume as per your tags you are using MS365
• Formula used in cell B2
=LET(
a,HSTACK($D$2:$D$8,
BYROW(SIGN(IFERROR(XMATCH($E$2:$K$8,TEXTSPLIT(A2," ")),0)),LAMBDA(x,SUM(x)))),
@SORT(a,2,-1))
Or, Using <kbd>MAP()</kbd>
• Formula used in cell B2
=MAP(A2:A6,LAMBDA(a,
LET(b,TEXTSPLIT(a," "),
d,BYROW(SIGN(IFERROR(XMATCH(E2:K8,b),0)),LAMBDA(c,SUM(c))),
e,HSTACK(D2:D8,d),@SORT(e,2,-1))))
Another alternative approach without using <kbd>LAMBDA()</kbd>
• Formula used in cell B2
=LET(
a,N(IFERROR(XMATCH($E$2:$K$8,TEXTSPLIT(A2," ")),0)<>0),
b,HSTACK($D$2:$D$8,MMULT(a,SEQUENCE(ROWS(a),,,0))),
@SORT(b,2,-1))
If you want a single array formula.
=MAP(A2:A6,LAMBDA(m,LET(
a,N(IFERROR(XMATCH($E$2:$K$8,TEXTSPLIT(m," ")),0)<>0),
b,HSTACK($D$2:$D$8,MMULT(a,SEQUENCE(ROWS(a),,,0))),
@SORT(b,2,-1))))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论