Hi @RynerZ
Assuming Excel 365 with dynamic arrays + FILTER and LET functions:
in Sheet3!B1:
=LET(
SplitString,FILTERXML("<r><n>" & SUBSTITUTE(Sheet1!A1,",";"</n><n>") & "</n></r>"; "//n"),
Lookup,VLOOKUP(SplitString,Sheet2!A1:B100,2,FALSE),
FilterOutNA,FILTER(Lookup,ISNUMBER(Lookup)),
"[" & TEXTJOIN(",",, IFERROR(FilterOutNA,"No match")) & "]"
)
Corresponding workbook avail. here
Feel free to upvote Add a Split dynamic array function on Excel UserVoice