Search code examples
excelexcel-formulavlookup

Find the Rank 01 Matching element against the Given Key word

I have two tables, Table two with code and Key words.

enter image description here

Table 01

enter image description here

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

Solution

  • 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

    enter image description here

    • 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 MAP()

    enter image description here

    • 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 LAMBDA()

    enter image description here

    • 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.

    enter image description here

    =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))))