Look up two parts of a string in a cell

HelloWassimN​,

You can’t use XLOOKUP directly on the full text because it looks for an exact match. In this case, you need to split the text into separate items and then look each one up.

If A2 contains:
Apples and Oranges

And your lookup table is:
D2:D10 = fruits
E2:E10 = TRUE/FALSE

Try this:

=XLOOKUP(TEXTSPLIT(A2," and "),D2:D10,E2:E10,"")

That will return the result for both Apples and Oranges.

If you want everything in one cell:

=TEXTJOIN(", ",TRUE,XLOOKUP(TEXTSPLIT(A2," and "),D2:D10,E2:E10,""))

If you also want to return the fruit names themselves:

=TEXTJOIN(", ",TRUE,TEXTSPLIT(A2," and "))

And if you want a logical check:

Any TRUE:
=OR(XLOOKUP(TEXTSPLIT(A2," and "),D2:D10,E2:E10))

All TRUE:
=AND(XLOOKUP(TEXTSPLIT(A2," and "),D2:D10,E2:E10))

This works in Excel 365 since XLOOKUP can handle arrays returned by TEXTSPLIT.