Google Sheets – Check if Two Columns Contain Same String of Numbers

formulasgoogle sheetsgoogle-sheets-arrayformularegex

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

Best Answer

=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

Related Topic