• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Multiple Condition Conditional Format



I have a list of names with an ID tag of either 1 or 2 against them and a second list of just names.

I am trying to apply conditional formatting to the second list based on two factors.

1) Does the name exist in the first list. I can do this with =match(Cell, Array, 0) no problem.

2) the colour should then depend on the ID tag of 1 or 2 which is on the same row and adjacent column of the name in the first list. e.g.

List 1
John Smith | 1 |
Sam Tucker | 2 |

Any help appreciated :)


Excel Ninja
You need to create two rules viz.
- Rule #1 for coloring cell if value is 1.
- Rule #2 for coloring cell if value is 2.


Hi Shrivallabha,

Yes I understand this but am unsure how to add the second condition. I was thinking something like this but this doesnt work as the second condition doesnt know what row the match is on.

e.g. for Tag 1: = AND(MATCH(cell,array,0)),(array2 = "1"))
Tag 2: = AND(MATCH(cell,array,0)),(array2 = "2"))


Peter Bartholomew

Well-Known Member
You simply need to look up the ID tag. I used the name 'tag' to refer to the formula:
= INDEX(IDtag, MATCH(cell,array,0))
Then the two conditional formats are based on the formulae:

Any errors (the name is not present) remain unformatted.


Hi Peter,

Thank you very much. I was experimenting with index match but forgot to apply the Boolean at the end.

Cheers :)