Forum Discussion

WassimN's avatar
WassimN
Copper Contributor
Apr 05, 2026

Look up two parts of a string in a cell

Hello All,

 

I know all about the XLOOKUP() but at times I need a special type of a look up.

 

if I have a cell with the text “Apples and Oranges” how can I write an XLOOKUP to evaluate the “Apples” and then evaluate the “Oranges” parts.

 

for example if I have a list of fruits in a column, then in the next column I have true if the fruit has seeds and false if the fruit does not have seeds.

 

now normally the XLOOKUP will look for the whole string, so if there is no fruit called “Apples and Oranges” it will fail and not return anything. But I do have “Apples” in one cell and it has true for having seeds, AND I do have “Oranges” and it has false for seeds (these are Naval oranges 🤪)

so the XLOOKUP will not return false.

 

how can I write an XLOOKUP to return “Apples”, “Oranges”, AND “Apples and Oranges”?

 

I thought of replacing the part after the And but then I’ll lose the fine parts, then I tried splitting the string on “AND” and that did not work at all. I guess you can’t run an XLOOKUP on an array part???

 

any help will be greatly appreciated!

excel
Formulas and Functions
office 365

2 Replies

  • Olufemi7's avatar
    Olufemi7
    Iron Contributor
    Apr 06, 2026

    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.