Spreadsheet: 129258d1321525929-combination-of-vlookup-and-hlookup-example3
Sheet: AFTER

i | 2011-01-01 00:00:00 | Name | FTE | ATT | PL | SL | UL | WD | Sun | Mon | Tue | Wed | Thu | Fri | Sat | Sun | Mon | Tue | Wed | Thu | Fri | Sat | Sun | Mon | Tue | Wed | Thu | Fri | Sat | Sun | Mon | Tue | Wed | Thu | Fri | Sat | Sun | Mon | Tue | Wed
 |  |  |  |  |  |  |  |  | 2011-12-31 00:00:00 | 2012-01-01 00:00:00 | 2012-01-02 00:00:00 | 2012-01-03 00:00:00 | 2012-01-04 00:00:00 | 2012-01-06 00:00:00 | 2007-01-07 00:00:00 | 2006-01-08 00:00:00 | 2005-01-09 00:00:00 | 2004-01-11 00:00:00 | 2003-01-12 00:00:00 | 2002-01-13 00:00:00 | 2001-01-14 00:00:00 | 2000-01-16 00:00:00 | 1999-01-17 00:00:00 | 1998-01-18 00:00:00 | 1997-01-19 00:00:00 | 1996-01-21 00:00:00 | 1995-01-22 00:00:00 | 1994-01-23 00:00:00 | 1993-01-24 00:00:00 | 1992-01-26 00:00:00 | 1991-01-27 00:00:00 | 1990-01-28 00:00:00 | 1989-01-29 00:00:00 | 1988-01-31 00:00:00 | 2011-12-26 00:00:00 | 2011-12-27 00:00:00 | 2011-12-28 00:00:00 | 2011-12-29 00:00:00 | 2011-12-30 00:00:00 | 2011-12-31 00:00:00
 |  |  |  |  |  |  |  |  | Dec | Jan | Jan | Jan | Jan | Jan | Jan | Jan | Jan | Jan | Jan | Jan | Jan | Jan | Jan | Jan | Jan | Jan | Jan | Jan | Jan | Jan | Jan | Jan | Jan | Jan | Jan | Jan | Jan | Jan | Jan | Jan
 | Availability
i |  | [NAME] | 0.9545454545454546 | 0.7727272727272727 | 1 | 1 | 1 | 15 | 7.12 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 19.12 | UA | BT | SL | UL | RL | PL | CD | TR | N/A
i |  | [NAME] | 0.9545454545454546 | 0.7727272727272727 | 1 | 1 | 1 | 15 | 7.12 | PL | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 19.12 | UA | BT | SL | UL | RL | PL | CD | TR | N/A
i |  | [NAME] | 0.9545454545454546 | 0.7727272727272727 | 1 | 1 | 1 | 15 | 7.12 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 19.12 | UA | BT | SL | UL | RL | PL | CD | TR | N/A
i |  | [NAME] | 0.9545454545454546 | 0.7727272727272727 | 1 | 1 | 1 | 15 | 7.12 | 8 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 19.12 | UA | BT | SL | UL | RL | PL | CD | TR | N/A
i |  | [NAME] | 0.9545454545454546 | 0.7727272727272727 | 1 | 1 | 1 | 15 | 7.12 | UL | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 19.12 | UA | BT | SL | UL | RL | PL | CD | TR | N/A
 |  |  |  |  |  |  |  |  | we assume current date is 01/01/2012 (cell K2) current date have to be compared from cell B2 by checking which cell in row 2 is equal to B2 the employee [NAME] (cell c5) should be at work today from 7 o clock (cell k5)  I need the formula which will color the cell B5 in green because [NAME] should be at work today. Same applies for [NAME] (B7) and [NAME] (B8) [NAME] and [NAME] should not be at work on 1st of january so the color in cells ([NAME] and B9) should be colored in red
 |  |  |  |  |  |  |  | here are the different criteria variations which can show if the employee should be at work on current day or not and color the cell accordingly:
 |  |  |  |  |  |  |  |  | 7.12 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 19.12 | employee is at work
 |  |  |  |  |  |  |  |  |  |  |  |  | UA | BT | SL | UL | RL | PL | CD | TR | N/A | employee is not at work
 | i |  | Name | FTE | ATT | PL | SL | UL | WD | Sun | Mon | Tue | Wed | Thu | Fri | Sat | Sun | Mon | Tue | Wed | Thu | Fri | Sat | Sun | Mon | Tue | Wed | Thu | Fri | Sat | Sun | Mon | Tue | Wed | Thu | Fri | Sat | Sun | Mon | Tue | Wed
 |  |  |  |  |  |  |  |  |  | 1900-01-31 00:00:00 | 2011-12-01 00:00:00 | 2011-12-02 00:00:00 | 2011-12-03 00:00:00 | 2011-12-04 00:00:00 | 2011-12-05 00:00:00 | 2011-12-06 00:00:00 | 2011-12-07 00:00:00 | 2011-12-08 00:00:00 | 2011-12-09 00:00:00 | 2011-12-10 00:00:00 | 2011-12-11 00:00:00 | 2011-12-12 00:00:00 | 2011-12-13 00:00:00 | 2011-12-14 00:00:00 | 2011-12-15 00:00:00 | 2011-12-16 00:00:00 | 2011-12-17 00:00:00 | 2011-12-18 00:00:00 | 2011-12-19 00:00:00 | 2011-12-20 00:00:00 | 2011-12-21 00:00:00 | 2011-12-22 00:00:00 | 2011-12-23 00:00:00 | 2011-12-24 00:00:00 | 2011-12-25 00:00:00 | 2011-12-26 00:00:00 | 2011-12-27 00:00:00 | 2011-12-28 00:00:00 | 2011-12-29 00:00:00 | 2011-12-30 00:00:00 | 2011-12-31 00:00:00
 |  |  |  |  |  |  |  |  |  | Dec | Jan | Jan | Jan | Jan | Jan | Jan | Jan | Jan | Jan | Jan | Jan | Jan | Jan | Jan | Jan | Jan | Jan | Jan | Jan | Jan | Jan | Jan | Jan | Jan | Jan | Jan | Jan | Jan | Jan | Jan | Jan
 | i |  | name | 0.9545454545454546 | 0.7727272727272727 | 1 | 1 | 1 | 15 | 7.12 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 19.12 | UA | BT | SL | UL | RL | PL | CD | TR | N/A
 | i |  | [NAME] | 0.9545454545454546 | 0.7727272727272727 | 1 | 1 | 1 | 15 | 7.12 | PL | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 19.12 | UA | BT | SL | UL | RL | PL | CD | TR | N/A