1

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.

Example:
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

Example

1

=ARRAYFORMULA(IF(LEN(B2:B&C2:C); IF(B2:B=C2:C; TRUE); ))

0


=ARRAYFORMULA(IF(LEN(B2:B&C2:C); REGEXMATCH(B2:B; C2:C); ))

0


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

0

  • these don't work for my situation. I have further explained my question with a screenshot – Marek Marhoul Jul 24 at 9:00
  • @MarekMarhoul answer updated - use 3rd formula – user0 Jul 24 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. – Marek Marhoul Jul 25 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 – Marek Marhoul Jul 31 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 at 0:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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