Find the Rank 02 Matching element against the Given Key word

July 5, 2023

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

Now I need to get the rank 02 separately

Table 01 With combination of Key words, SO need to find the 2nd Rank of height matching row of each description and need to give the rank two matching code as code against the Description.

Best regard
Indika

>Solution :

There is not much difference between the last post and this one, you need to wrap the SORT() function within TAKE() & TOROW()

• 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))),
     TOROW(TAKE(SORT(b,2,-1),2,1)))

Reference to last post of OP: Find the Rank 01 Matching element against the Given Key word

• Formula used in cell B2

=DROP(REDUCE("",A2:A6,LAMBDA(m,b,VSTACK(m,LET(
       a,N(IFERROR(XMATCH($E$2:$K$8,TEXTSPLIT(b," ")),0)<>0),
       c,HSTACK($D$2:$D$8,MMULT(a,SEQUENCE(ROWS(a),,,0))),
       CHOOSECOLS(TOROW(SORT(c,2,-1)),1,3))))),1)