1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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

    Hui...

  3. 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

Discussion in 'Ask an Excel Question' started by Tripp, Sep 13, 2018.

  1. Tripp

    Tripp Member

    Messages:
    73
    Hello,

    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 :)
    Tripp
  2. shrivallabha

    shrivallabha Excel Ninja

    Messages:
    1,909
    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.
  3. Tripp

    Tripp Member

    Messages:
    73
    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"))



    Tripp
  4. Peter Bartholomew

    Peter Bartholomew Active Member

    Messages:
    379
    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:
    =(tag=1)
    =(tag=2)

    Any errors (the name is not present) remain unformatted.
    Chirag R Raval likes this.
  5. Tripp

    Tripp Member

    Messages:
    73
    Hi Peter,

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

    Cheers :)

Share This Page