SOLVED

# Help with IF/Search formula

Occasional Contributor

# 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

2 Replies
best response confirmed by Hans Vogelaar (MVP)
Solution

# Re: Help with IF/Search formula

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

# Re: Help with IF/Search formula

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

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

Best Regards