how to pattern match over multiple column in excel-CodePudding

I have a excel sheet with two column-first column with some number and second column with the values for various other number including specified numbers in column one. The values in second column are present along with other values in comma separated format and the specified number is quoted in bracket. How do I extract the value for the specified number is column 1 from second column? For instance:

The data I have:

Output required:

Looking forward for reply and Thanks in advance

CodePudding user response：

If you have Excel 365, and your data is in columns A and B, you can use:

=LET(x,TEXTSPLIT(B1,","),
y, MATCH(TRUE,ISNUMBER(FIND(A1,x)),0),
z, TEXTSPLIT(INDEX(x,y),"("),
INDEX(z,1))

If you have an earlier version of Excel (Windows), you can use:

=LEFT(FILTERXML("<t><s>" & SUBSTITUTE(B1,",","</s><s>") & "</s></t>","//s[contains(.," & A1 & ")]"), FIND("(",FILTERXML("<t><s>" & SUBSTITUTE(B1,",","</s><s>") & "</s></t>","//s[contains(.," & A1 & ")]"))-1)