Search code examples
excelconditional-formattingformulas

What is the best way to update the match formula to conditionally format?

=MATCH(C$13, 'Vacation - Holiday'!$B:$B, 0)

C13 is the date 01/01/2016.

I am using this formula to conditionally format a cell in a calendar dashboard. It works great but I need the lookup array to be dependent on the name listed in cell B15.

Can I use AND(Match())? I have tried many things and feel so close but so far.

"Meg" is the header of the "B" column on sheet 'Vacation - Holiday'!$B:$B. There are names in columns A - J.

A     B           C           D        E            F         G           H
              1/1/2016   1/4/2016   1/5/2016    1/6/2016    1/7/2016    1/8/2016
     Meg
Solution

  • The formula you want is:

    =ISNUMBER(MATCH(C$13,INDEX(Sheet6!$A:$J,0,MATCH($B15,Sheet6!$1:$1,0)),0))