Search code examples
excelexcel-formulastring-comparison

Compare values in one column with another column

A2 has cotton,leather

B2 has Leather,wool,cotton

I need to see if all values in A2 is present in B2 irrespective of case or order or any extra values in B2.

So i need a formula which says present in C2 for the above example.

=IF(AND(ISNUMBER(SEARCH(","&LOWER(TRIM(A2))&",", ","&LOWER(TRIM(B2))&","))), "present", "not present")

this formula works if it's in order. but for the above example it says not present.

Few other examples are:

Column A Column B Result
cotton,leather Leather,wool,cotton Present
red red,blue Present
cotton candy,pink pink,red,cotton candy Present
blue red,yellow Not Present
Infant,toddler Baby,Toddler Not Present
Adult,Baby,Taller,Kidder Adult,Kid Not Present
Steel Alloy Steel,Wood Not Present
Solution

  • Using TEXTSPLIT() & SEARCH()

    enter image description here

    • Formula used in cell C2

    =IF(
    OR(
    ISERROR(
    SEARCH(
    ","&TEXTSPLIT(A2,",")&",",","&B2&","))),
    "Not ","")
    &"Present"

    Or,

    enter image description here

    • Formula used in cell C2

    =IF(
    AND(
    ISNUMBER(
    SEARCH(
    ","&TEXTSPLIT(A2,",")&",",","&B2&","))),
    "","Not ")
    &"Present"

    Try using FILTERXML()

    enter image description here

    • Formula used in cell C2

    =IF(
    AND(
    ISNUMBER(
    SEARCH(
    ","&FILTERXML("<m><b>"&SUBSTITUTE(A2,",","</b><b>")&"</b></m>","//b")&",",","&B2&","))),
    "Present","Not Present"
    )

    Note: Based on your Excel Version one needs to hit CTRL+SHIFT+ENTER while exiting the edit mode.

    To learn more on FILTERXML() highlighly recommended post exclusively by JvdV Sir:

    Excel - Extract substring(s) from string using FILTERXML