Remake formula to find partial matches

[MIKA1122]

=MATCH(COUNTA(B1:H1),LEN(SUBSTITUTE(INT(MMULT(IFERROR(10^MATCH($M$1:$AC$120000,B1:H1,0),0),TRANSPOSE(COLUMN($M$1:$AC$120000)^0))/10),0,"")),0)

This formula is looking for matches but full exact matches from one table in another one as in attached file

i need it to be remade so it will be able to find partial matches of 6 or 5 or 4 or 3 or 2 or 1 with same logic as it is now
order of found matches in the row can be any order
here is an exlanation
there are 2 tables
the formula finds a full exact match of the rows from table at the left in table in the right and returns the number of the first row where this match was found
i attached the working file and you can actually see this in action


right now i need this formula to be redone or may be a brand new one which will perform the same task but
will be looking not for exact full matches but partial matches


for example

if in row in left table there are 1 2 3 4 5 i need a formula which will find any 4 numbers from those for example 1 2 3 5

and returns me number of the first row where it was found

and this means i need ability to define each time prior the calculation how many matches i expect each time
in this case 4 matches

i hope this cleares the meaning of the formula

thank you

Mod edit: xpost: https://www.excelforum.com/excel-ge...he-row-and-quantity-of-matches-by-choice.html

 

[bosco_yip]

Try,

1] Create a dropdown list in J2 for the Selection of no. 1 to no. 7

2] In "Row no. 1st matched", K2 formula copied down :

=IFERROR(AGGREGATE(15,6,$I$1:$I$10/(MMULT(COUNTIF(B1:H1,$M$1:$AC$10),{1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1})=J$2),1),"")

3] In "Row number all matched", L2 array (Ctrl+Shift+Enter) formula copied down :

=IFERROR(TEXTJOIN(", ",1,IFERROR($I$1:$I$10/(MMULT(COUNTIF(B1:H1,$M$1:$AC$10),{1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1})=J$2),"")),"")

 

[MIKA1122]

thank you Bosco
it gives some error here
not showing all matched row in your file

 

[bosco_yip]

thank you Bosco
it gives some error here
not showing all matched row in your file

Please indicate for the error location

Regards

 

[MIKA1122]

attaching screenshot
i downloaded it and this is how it shows

 

[MIKA1122]

marked with black line

 

[bosco_yip]

attaching screenshot
i downloaded it and this is how it shows

It is because your Excel version don't have TEXTJOIN function of which available in office 365 and Excel 2019 only.

Please Google in searching of "Excel TEXTJOIN function" for more detailing.

Regards

 

[MIKA1122]

oh i see
thanks a lot
will do

 

[Peter Bartholomew]

Impressive! I only began to understand the problem by working through @bosco_yip's solution!
I finished up with

= LET(
  matches, COUNTIF(currentRow, table),
  count,   SUMROWS(matches),
  XLOOKUP( TRUE, count=select, k, "") )

which worked on my version of Excel but pretty much nowhere else.

 

[MIKA1122]

thank tou Peter
can you please attach excel working file with your solution , please?

 

[Peter Bartholomew]

The file is of no use to you at present because it requires the latest versions of Microsoft 365 and the FastExcel addin (which provides additional highly recommended functions such as SUMCOLS and ACCUMULATE) from Charles Williams. I could reconstruct the file from the information posted but I would recommend using @bosco_yip's solution.

 

[bosco_yip]

Our Moderator have give you a warning regarding your cross-post behavior,

however it is noted that you failed to give response and neglect to our advice.

Since your question has solved, this post closed.

Regards
Bosco