google spreadsheet IF a cell contains some words included in another cell

Question

I'm trying to check if a cell contains some words included in another cell, if yes it should say "Valid". For example, if cell A2 is Jon Doe Street and cell B2 is 230 Jon Doe it should say "valid" I'm using this formula =IF(ISNUMBER(SEARCH($B2,C2)),"Valid","") but it only works for some rows. This is a sample you can check https://docs.google.com/spreadsheets/d/1S5m_kV0td9_nq8vWZGkSneCEXGnLHL_af1hlzn2sz0I/edit?usp=sharing How can i fix it? thanks!

Answer 1

use:

=IF(SUM(BYCOL(SPLIT(B2," "),LAMBDA(ax,IFERROR(--REGEXMATCH(C2,ax)))))>=2,"VALID",)

arrayformula

=MAP(B2:B,C2:C,LAMBDA(abx,cx,IF(SUM(BYCOL(SPLIT(abx," "),LAMBDA(ax,IFERROR(--REGEXMATCH(cx,ax)))))>=2,"VALID",)))

-