Conditional Formatting based on 3 different cell values (criteria)

Hi there,

I would like to have a conditional formatting formula which can highlight a value and the top and left labels that correspond to it.

Here is my example :

Drop-down lists using Validation
Which Item ? __Bread___ (in cell M10)
Which Month ? __Feb_____ (in cell M11)

Result (using a combination of Match and Index)
Amount = __75_____ (in cell M13)

Jan Feb Mar Apr May Jun
Eggs 75 31 83 59 38 66
Bread 15 66 95 25 63 97
Milk 36 58 18 31 58 46
Cheese 25 75 62 38 95 28
Meat 14 45 44 49 45 54
Veggies 87 96 81 65 25 53
Kleenex 94 32 37 88 38 95
Shampoo 19 52 64 61 76 85

Conditional Formattings applied to entire table above
1) =NOT(ISERR(SEARCH($M$10,C10)))…..(highlights column title “Feb” correctly)
2) =NOT(ISERR(SEARCH($M$11,C10))) …..(highlights row title “Bread” correctly)
3) =NOT(ISERR(SEARCH($M$13,C10))) …..(highlights all 3 amounts of “75” incorrectly – need it to only highlight the 75 that corresponds to the intersection of item “Bread” and month “Feb”)

What formula (or combination of formulas can I use to achieve this ?

In short, I require conditional formatting to highlight an amount and it’s corresponding top label and side label based on selections made using a drop-down list.

Thanks in advance.

Thanks for your reply Rylo !

You were correct about the 66 i/o 75 (I had originally written out my post with just 3 rows and 3 col's of data - whereby I had manually adjusted the feb bread value to be 75 - but then just before posting, I copied and pasted the entire data table from my S/S to show all rows and cols...since the formulas were refering to more than just 3 rows and cols).

I have now attached a zip file containing the sheet.

Appreciate your help.

Later

Thanks Starguy ! You are indeed a genius...and your solution is so elegant....I'm truly impressed

I honestly didn't think it would be that simple....however, I still don't understand how the third conditional format works i.e. the "3) Range D11:I18 has =AND($C11=$M$10,D$10=$M$11)". Would you be able to explain how it does, what it's doing so well ?

Thanks again,

Later

Hi

The AND statement will evaluate to TRUE when the value in column C for that row matches the product selected in M10 and the value in row 10 for the cell column equals the value in M11. If the result is TRUE then the conditional formatting will apply.


rylo