XLOOKUP function is failing

Question

I'm trying to use xlookup function to get the corresponding values, if the certain text is matching. Following is the example, trying to use. If not right function, would you please point to the right one?

Function:

=XLOOKUP("*"&D1&"*",A1:A4,B1:B4,"Not found",2)

Any support in this regard is really appreciated!

Below is the table in excel:

Answer 1

If I understand your case correctly you're trying to search for a case sensitive match of both comma separated strings in column D to match the space separated strings in column A and return the value of column B for the matching row.

For a single line search you could use: =FILTER(B$1:B$5,MMULT(--ISNUMBER(FIND(" "&TEXTSPLIT(TEXTAFTER(", "&D$1:D$5,", ",{1,2}),", ")&" "," "&A1&" ")),{1;1})=2,"Not found")

For spilling the results for a range you could use:

=LET(A, A1:A5,
     B, B1:B5,
     D, D1:D5,
     s, " ",
MAP(A,LAMBDA(M,FILTER(B,MMULT(--ISNUMBER(FIND(s&TEXTSPLIT(TEXTAFTER(", "&D,", ",{1,2}),", ")&s,s&M&s)),{1;1})=2,"Not found"))))

This formula works if both comma separatedstrings of D1:D5 have a (case sensitive) match to the space separated strings in A1:A5.

Or another way:

=LET(A,MAP(A1:A5,LAMBDA(m,CONCAT(SORT(TEXTSPLIT(m,," ")&"|")))),
     D,MAP(D1:D5,LAMBDA(m,CONCAT(SORT(TEXTSPLIT(m,,", ")&"|")))),
BYROW(EXACT(A,TRANSPOSE(D)),LAMBDA(r,FILTER(TOROW(B1:B5),r,"not found"))))

If it could be more than two separated strings, change it to:

=LET(A, A1:A6,
     B, B1:B6,
     D, D1:D6,
     s, " ",
MAP(A,LAMBDA(M,LET(n,ROWS(TEXTSPLIT(M,," ")),FILTER(B,MMULT(--ISNUMBER(FIND(s&TEXTSPLIT(TEXTAFTER(", "&D,", ",SEQUENCE(,n)),", ")&s,s&M&s)),SEQUENCE(n,,,0))=n,"Not found")))))

and

=LET(A,MAP(A1:A6,LAMBDA(m,CONCAT(SORT(TEXTSPLIT(m,," ")&"|")))),
     D,MAP(D1:D6,LAMBDA(m,CONCAT(SORT(TEXTSPLIT(m,,", ")&"|")))),
BYROW(EXACT(A,TRANSPOSE(D)),LAMBDA(r,FILTER(TOROW(B1:B6),r,"not found"))))

Answer 2

Use partial match either before comma or after comma. Try-

=XLOOKUP("*"&TEXTBEFORE(D1,",")&"*",$A$1:$A$4,$B$1:$B$4,"Not Found",2)