Forum Discussion

Copper Contributor

Feb 10, 2022

Solved

Compare cell value againt a string of text or data separated by comma in another cell

I have 2 lists of data in 2 different columns as "Set 1" and "Set 2". The value in cell (A2) under "Set 1" need to be compared with text in cell (B2) under "Set 2" and it can have comma separated val...

excel

HansVogelaar
Feb 14, 2022
vijay_kiran

Sorry change it to

=IF(ISNUMBER(FIND(", "&A2&", ",", "&B2&", ")),A2,"")

HansVogelaar

MVP

Feb 10, 2022

vijay_kiran

In B2:

=IF(ISNUMBER(FIND(","&A2&",",","&B2&",")),A2,"")

Fill down.

vijay_kiran
Copper Contributor
Feb 14, 2022
HansVogelaar

Thank you, that is almost what I was looking for. I believe there a small tweaking required though. Its not picking the value if it is not the first one in the cell. Just to clarify from my example, in row 2, its not picking PB and giving a blank as that was not the first one in the cell but its helped in row 3 cause PB is the first value in it and returning it. Same problem in row 4 & 5 and working in row 6 again and so on.
- HansVogelaar
  MVP
  Feb 14, 2022
  vijay_kiran
  
  Sorry change it to
  
  =IF(ISNUMBER(FIND(", "&A2&", ",", "&B2&", ")),A2,"")
  - vijay_kiran
    Copper Contributor
    Feb 14, 2022
    HansVogelaar
    Thanks much. That worked like a magic