I am working with multiple data sets where a cell can have multiple values. I want to apply a conditional formatting on a cell with a formula to highlight the cell when it contains a keyword.
I have applied the below formula in conditional formatting, but it doesn’t work. I have verified that the formula returns TRUE.
I want to highlight the cell if it contains “Red”. Problem now is that it ONLY works if the return value “Red” is the first occurence.
=ISNUMBER(FIND("Red",TEXTJOIN(",",TRUE,IFERROR(VLOOKUP(TEXTSPLIT(H8,CHAR(10)),Activities!$B:$C,2,FALSE),""))))
Cell Value Example
Apple
Pear
Lookup Table Example that works:
| Column B | Column C |
|---|---|
| Apple | Red |
| Pear | Green |
Lookup Table Example that DOESN’T works:
| Column B | Column C |
|---|---|
| Apple | Green |
| Pear | Red |
Basically what I envisioned with the formula is:
- To look up both Apple and Pear separately, TEXTSPLIT used in VLOOKUP
- Once I got the results, merge back using TEXTJOIN
- If the return value contains “Red”, highlight the cell in red.
Any advise on this?
Edited the post to elaborate more on the problem and scenario.
9
Not sure if I understood properly, but just in case I want to give it a shot:
Case 1: if target cell equals red then highlight adjacent cell
The formula of the CF rule is : =COUNTIF(C1;"Red")>0
Case 2: if any of the cells in target column equals red then highlight whole adjacent column
The formula of the CF rule is : =COUNTIF($C$6:$C$7;"red")>0
In both cases make sure the formula applies to whole fruits column
Both formulas will check if the cells is exactly the value red (without differentiating between uppercase and lowercase)
Also, please note CASE 2 works even if the value red is not in the first position of the column:
3
Try this:
=SUMPRODUCT(COUNTIF(H8, FILTER("*"&Activities!$B:$B&"*", Activities!$C:$C="Red", "#N/A")))>0
Using TextSplit to make a Dynamic Spill list of the items in cell H8 does not appear to function properly in a Conditional Formatting function.
However, using Filter on the Activities sheet does seem to work.
So, instead of checking each line in H8, finding its value in the Activities sheet, and then looking to see if that list contains “Red”, we invert it: We Filter to get the values from the Activities sheet that map to “Red”, and then we check to see if any of those values exist in Cell H8.
(Note that this will, unfortunately, have issues with Substrings. For example, if “Straw” is Red, but “Strawberry” is Green, then it will give a false-positive for “Strawberry”, when it finds “Straw” at the start…)
{EDIT} The Substring issue was annoying me, so I decided to make things more rigorous:
=SUMPRODUCT(COUNTIF(H8, FILTER("*"&Char(10)&Activities!$B:$B&Char(10)&"*", Activities!$C:$C="Red", "#N/A"))+COUNTIF(H8, FILTER(Activities!$B:$B&Char(10)&"*", Activities!$C:$C="Red", "#N/A"))+COUNTIF(H8, FILTER("*"&Char(10)&Activities!$B:$B, Activities!$C:$C="Red", "#N/A"))+COUNTIF(H8, FILTER(Activities!$B:$B, Activities!$C:$C="Red", "#N/A")))>0
This will check for: contains the Activity value with a New Line at the start and end; starts with the Activity value followed by a New Line; ends with a New Line followed by the Activity value; or is exactly the Activity value. That should eliminate any substrings.
1
Update 2024-09-14
Tested on a larger data set (~ 10,000, 30 in H8), filtering by ‘Red’ first, seems to work quickly on my Mac:
=SUM(N(FILTER(B1:B10000, C1:C10000 = "Red") = TEXTSPLIT(H8, CHAR(10)))) > 0
6
Lookup table: M18:N19
Input table: I18:I19
Formula in K18 and drag down
If a red marked fruit is in cell text return value is TRUE
=LET(inred,IF($N$18:$N$19="red",$M$18:$M$19,"ß"),
pos,IFERROR(FIND(inred,I18),0),
IF(SUM(pos)>0,TRUE,FALSE))
This is the screenshot of applying it on the sheet:
2