Some time ago I have put together a perfectly working formula but now it's not working and I can't tell why (I may have changed something and don't remember that).

Column B contains data that I need to check whether they appear on the same row in column C. These two columns aren't identical though, data in column B are part of data in column C

This is what I have now

=ARRAYFORMULA(IF(C2:C<>0; isnumber(SEARCH($B2;$C2));""))

but the output is TRUE even though the two cells I need to compare aren't in the same row but I need to compare B2 to C2, B3 to C3, etc.

Row 2 and 3 should be as is since there is nothing in column C in that row
Row 4 and 5 should say FALSE because data in column C in the same row don't match



1 Answer 1






 IF(LEN(C1:C)*LEN(B1:B); REGEXMATCH(C1:C; B1:B)); ))


  • these don't work for my situation. I have further explained my question with a screenshot Jul 24, 2019 at 9:00
  • @MarekMarhoul answer updated - use 3rd formula
    – user0
    Jul 24, 2019 at 19:01
  • This works perfectly, thank you! Just a little change had to be done to the starting row from 1 to 2 in your formula. Jul 25, 2019 at 5:13
  • This formula (#3) stopped working, the output is always FALSE and I have no idea what went wrong. I have tried copying the formula freshly from your answer so I can tell for sure whether I have mistakenly changed anything or not but that didn't help. Is it possible that something in Google Sheets has changed that would prevent this formula from working? Also when the data are identical, output is false Jul 31, 2019 at 10:02
  • @MarekMarhoul can you show me the faulty formula in a copy of your sheet? (if this is still the issue)
    – user0
    Aug 29, 2019 at 0:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.