CODEWITHSUNDEEP
google-sheets
Amr
Amr

Reputation: 37

Google-sheets – Conditional Format if cell in one column contains partial match from another column

I am trying to find a custom formula to highlight a cell from one column (A) if it contains a partial text from another column (C)

I created a sample spreadsheet: https://docs.google.com/spreadsheets/d/10m3lHi45BFtKuvDKUbOFAkxjAOhr0VtAnWASwgFIVGY/edit?usp=sharing

What I tried so far is this =IFERROR(IF(MATCH(""&INDIRECT("$A1")&"",$C1,0)>0,true,),false)

but that only works with the first cell also tried REGEXMATCH and search but couldn't come up with the right formula

Upvotes: 0

Views: 902

Answers (2)

Martín
Martín

Reputation: 10084

You can also try with this, it joins the whole C column and matches with REGEXMATCH. The second parenthesis excludes when the cell is empty:

=REGEXMATCH(JOIN(",",C:C),A1)*(A1<>"")

Upvotes: 3

rockinfreakshow
rockinfreakshow

Reputation: 29886

use:

=ARRAYFORMULA(MAX(IFERROR(SEARCH(A1,TEXTJOIN(" ",TRUE,$C:$C),1)),)*(A1<>""))

enter image description here

Upvotes: 2

Related Questions