1

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
Mayukh Bhattacharya
  • 12,541
  • 5
  • 21
  • 32
N S
  • 91
  • 7

1 Answers1

2

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

Mayukh Bhattacharya
  • 12,541
  • 5
  • 21
  • 32