Forum Discussion

reevesgetsaround's avatar
reevesgetsaround
Brass Contributor
Oct 11, 2022
Solved

Help with IF/Search formula

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

excel
Formulas and Functions
  • mtarler's avatar
    mtarler
    Oct 11, 2022

    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.

2 Replies

  • mtarler's avatar
    mtarler
    Silver Contributor
    Oct 11, 2022

    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.

    • reevesgetsaround's avatar
      reevesgetsaround
      Brass Contributor
      Oct 12, 2022
      And here's me thinking it would just be a simple tweak!

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

      Best Regards