Help with IF/Search formula

[reevesgetsaround]

Afternoon everyone,

I'm having a slight issue with a formula. I need a table array (as shown in the attachment), with a "Yes" under any corresponding matching numbers. It works fine until it gets to double figure numbers, for example in the first row for the figure 12, it is also returning a match for both 1 and 2. Is there any adjustment I can make to the formula wherein it only matches the exact figure?

Any help is always appreciated.

Kind Regards

S

[#2 · Accepted solution]

@reevesgetsaround In the attached I did it both using an array formula that makes the whole table at one time and using a formula that applies to 1 cell at a time and needs to be fill down & right. Here are the formulas I used:

single cell:

=LET(in,D$22,group,$B23,
     IF(ISNUMBER(in),
         LET(gtemp,","&TEXTJOIN(,,IFERROR(--MID(group,SEQUENCE(LEN(group)),1),","))&",",
             IF(ISNUMBER(SEARCH(","&in&",",gtemp)),"Y","")),
         IF(ISNUMBER(SEARCH(in,group)),"Y","")))

 whole table:

=LET(colList,$D$12:$Q$12,rowList,$B$13:$B$20,
grouplists,SCAN("",rowList,LAMBDA(p,i,","&TEXTJOIN(,,IFERROR(--MID(i,SEQUENCE(LEN(i)),1),","))&",")),
MAKEARRAY(ROWS(rowList),COLUMNS(colList),LAMBDA(r,c,
     LET(in,INDEX(colList,c),IF(ISNUMBER(in),
              IF(ISNUMBER(SEARCH(","&in&",",INDEX(grouplists,r))),"Y",""),
              IF(ISNUMBER(SEARCH(in,INDEX(rowList,r))),"Y",""))))))

In both cases I convert the text in col B to a  list of numbers found inside.  In the upper that is line 3 and in the second it is line 2.  And then I check if the column is a number and then search for numbers or if not then do a text search.

[#3]

And here's me thinking it would just be a simple tweak!

This works perfectly, you're a genius. Thank You.

Best Regards