Excel Conditional Formatting For Multiple Matches

1
votes

In this Excel, I want to set a conditional formatting such as shown in these picture attached below:

Screenshot

I have set a conditional formatting at row C such as followed to highlight the cells when it is below value / refer to remark in the attached pics :

=IF(B3="A",13,IF(B3="B",11,IF(B3="C",9)))

However, when I evaluate the conditional formatting, although I set B3 to the "C", it will keep evaluate to the first condition , that is "A".

What should I do to let the conditional formatting to format to its own matches; highlight row C when it is below 13 "Product A" , highlight row C when it is below 11 "Product B" , etc.

1
excelvba
If you have lots of Products (especially if you need an easy way to add/remove/modify Products) then you may find it easier to create a table with Product and "Highlight-when-less-than" values, and swap the IF block from Pᴇʜ's excellent answer out for a VLOOKUP - Chronocidal
@Chronocidal , do you have an example of Vlookup ? - Alan QA
Lots of examples available from Microsoft. You would want to look up the Product in a table, and return the "less than" value (e.g. "A" returns 13 or "C" returns 9) - Chronocidal
@AlanQA I improved my answer by an example. - Pᴇʜ

1 Answers

3
votes

In your example select range C2:C7 and add a conditional formatting with the formula

=$C2<IF($B2="A",13,IF($B2="B",11,IF($B2="C",9,FALSE)))

As Chronocidal pointed out in his comment:
If you have not only 3 products but even more, you can use a list of products and their according "less than" values in combination with a VLOOKUP function:

=$C2<VLOOKUP($B2,$F:$G,2,FALSE)

Note that $F:$G must point to the list of products and "less than" values (see picture below):

enter image description here

This way you can easily add more products to your list without the need to adjust your formula. Also you could easily change the "less than" value for each product without adjusting your formula.