Google-sheets – Conditional formatting with multiple names in list

conditional formattingformulasgoogle sheetsgoogle-sheets-arrayformularegex

Col A2:C15 contain a list of names. I need to enter those names in various combinations into one of 15 other cells on formatted like:

H3 = John, Steve, Bob, Dan

Is there a way to use conditional formatting to highlight the cells in A2:C15 based on the list of the names in other cells? I can get it to highlight a cell with just one name, but nothing after the "," is formatted.

Best Answer

=ARRAYFORMULA(REGEXMATCH(A1,TEXTJOIN("|",1,TRIM(SPLIT($H$3, ",")))))

Related Solutions

Google-sheets – Apply conditional formatting to one column

Conditional Formatting(Custom Formula):

=$A$1

Apply to:

A:A

Google Sheets – How to Use IF, AND Conditional Formatting

in this conditional-formatting-scenario is =ArrayFormula() kind of not necessary.
to hide #N/A you can use =IFERROR()
to avoid #N/A you can use =ISNUMBER =ISDATE =ISBLANK =ISTEXT etc.
and this custom formula is what you seek:

=IF( AND(ISNUMBER(J1:J);
         NOT(ISBLANK(L1:L))); COUNTIFS(J1:J; ">0"; 
                                       K1:K; ">"&DATE(0;1;1)))

it checks if J has number
and L is not blank
and then it checks if J>0
and K >1st january of year 0.

be sure you have right formatting otherwise it wont work:

update 1:

https://docs.google.com/spreadsheets/d/

update 2:

https://docs.google.com/spreadsheets/d/ where custom formula is:

=IF( AND(ISNUMBER(K2), 
         ISBLANK(M2), 
         ISDATE(L2)), COUNTIFS(K2, ">0", 
                               L2, ">"&DATE(0,1,1)))