Find matches from one list in another

[Vic320]

Hi,

   I am a Quality Engineer and have been working on a project to find out if minor defects in parts cause parts to fail later in the production process.  I have an inspector inspecting the parts as they are produced and noting minor defects and recording their serial number. These parts then get added to work orders that can contain up to ten of these parts.  If a failure is detected later, an engineer evaluates the problem and records the serial numbers of the failed parts. 

   I now have two lists, one that is a list of single units that show the minor defects found and another list that is base on the work order which could have up to 10 serial numbers in a single field. The engineers have been recording the serial numbers of the failed units in a single field by work order - sometimes delimited them with a comma, but sometimes with just a space or a line break.     

   I am struggling to come up with a formula that will search all the serial numbers in the failed list and see if there are any matches in the minor defects list (or vise versa). All I need is a "TRUE/FALSE" that a work order or serial number shows up on both lists and I can take it from there.  It would be easy if the engineers recorded each serial number on the failed list individually, but no joy. Thanks for any help you can provide. 

  -Victor

[#2]

@Vic320 

=BYROW(A2:A13,LAMBDA(row,ISNUMBER(SEARCH(row,TEXTJOIN(" ",,SUBSTITUTE(SUBSTITUTE($D$2:$D$11,CHAR(10),""),",",""))))))

You can try this formula which spills the results.

=ISNUMBER(SEARCH(A2,TEXTJOIN(" ",,SUBSTITUTE(SUBSTITUTE($D$2:$D$11,CHAR(10),""),",",""))))

Without LAMBDA you can try this formula which doesn't spill but can be copied down.

Both formulas return TRUE/FALSE (WAHR, FALSCH in german).

[#3 · Accepted solution]

@Vic320 

My function slightly different from @OliverScheurich :

=ISNUMBER(MATCH("*"&A2:A15&"*",""&G$2:G$23,0))

or

=ISNUMBER(XMATCH("*"&A2:A15&"*",""&G$2:G$23,2))

[#4]

@dscheikey  I ended up using your solution, mostly because it was easier for me to understand but it works great.  Thank you!