Conditional Format - if a cell's exact value is found in a range of cells then apply condition

[Mandy_]

Hi,

I would like some help please. I’m not sure what formula I should be using.

I’ve created a priority matrix. Using Microsoft 365. Image below.

I have created the tasks list, and when I enter data in the list the tasks then appear in the correct quadrants depending on the yes/no answers for urgent/important.

As I have been unable to work out how to delete the task from the quadrant itself – for if I do delete the task from the quadrant, then any future tasks won’t get copied across from the list. So I am wanting to create a conditional format rule to highlight the completed tasks in the list so I can easily see which lines to clear.

I have set up the helper columns to reflect the quadrants, and am using the formula in the bc and bd columns =IF(LEN($B8)>0,(INDEX($K$8:$K$67,MATCH($C8,range3,0),1)))

So – if any value is recorded next to the task in the quadrant (i.e. column B and column F,) then the helper column will display the task name, otherwise false.


My question from here then is – if the cells in the helper columns display text, find the text in column K and highlight.


I've spent nearly a week on this! and I can get the result if I use the formula

=$K8=$BC$8 where the conditional rule applies to $K$8:$O$67

=$K8=$BC$9

=$K8=$BC$10

etc and in the screen shot, I've only applied the rule =$K8=$BC$8, I haven't applied for the other cells, so task 2 should also be highlighted if my formula was correct. Which it isn't!


I was hoping with 60 lines (30 lines per column) – I was trying to have an encompassing formula rather than setting a conditional rule 60 times!

I’ve also tried =$K8=$BC$8:$BD$39

And I’ve tried sumproduct, sumif, countif – but I am new to excel and unsure of the syntax’s and I’m not quite understanding how to get the result I am after.

If anyone had any ideas I’d really appreciate it.



Thank you for your help.

 

[aRandomHelper]

Something like this maybe?

Book1.xlsm
	K	L	M	N	O																																								BC	BD
7	Header1	Header2	Header3	Header4	Header5
8	Task1																																												Task1	Task2
9	Task2																																												Task5	Task9
10	Task3
11	Task4
12	Task5
13	Task6
14	Task7
15	Task8
16	Task9
17	Task10
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
K8:O17	Expression	=COUNTIF($BC:$BD,$K8)>0	text	NO

 

[Mandy_]

Thank you! This solved it.

May I ask please - so I can understand, in your formula the columns $BC:$BD are without a row number, why is this?

 

[aRandomHelper]

columns $BC:$BD are without a row number, why is this?

That means it's referring to the entire columns BC and BD. You can also restrict the range to $BC$8:$BD$39, for example, if there are parts (above or below) you don't want the formula to search for a match.

 

[Mandy_]

That means it's referring to the entire columns BC and BD. You can also restrict the range to $BC$8:$BD$39, for example, if there are parts (above or below) you don't want the formula to search for a match.

Thank you for explaining. I really appreciate your help.