Formula to Search multiple multi line cells for items in column A and replace with items with values-CodePudding

Formula in B1:

=TEXTJOIN(CHAR(10),,XLOOKUP(TEXTSPLIT(A1&CHAR(10),CHAR(10),,1),E:E,D:D,"Not Found"))

You can also choose to do this for the whole range at once:

Formula used:

=LET(x,CHAR(10),MAP(A1:A2,LAMBDA(y,TEXTJOIN(x,,XLOOKUP(TEXTSPLIT(y&x,x,,1),E:E,D:D,"Not Found")))))

CodePudding user response：

Replace Delimited Substrings (`BYROW`)

=LET(dlData,$A$2:$A$6,slData,Sheet2!$B$2:$B$13,svData,Sheet2!$A$2:$A$13,
    cDelimiter,CHAR(10),IgnoreEmpty,1,
BYROW(dlData,LAMBDA(dlCell,
    LET(drData,TEXTSPLIT(dlCell,cDelimiter,,IgnoreEmpty),srData,XLOOKUP(drData,slData,svData),
        TEXTJOIN(cDelimiter,IgnoreEmpty,IFERROR(srData,drData))))))

Replace Delimited Substrings (BYROW)

Replace Delimited Substrings (`BYROW`)