Reputation: 111

Excel conditional formatting doesn't apply though formula returns TRUE

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.

Upvotes: 3

Answers (4)

nkalvi

Reputation: 2614

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

Upvotes: 1

Black cat

Reputation: 6281

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:

Upvotes: 0

Chronocidal

Reputation: 8081

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.

Upvotes: 1