Combination of VLOOKUP and HLOOKUP

Find my schedule template example attached.

I was wondering how to combine VLOOKUP and HLOOKUP in single formula which i can use in order to make automatic changing of the background of the cells in column "B" so they will display green background if employee is at work today and to display red background if employee is NOT at work today.

when employee is at work it is marked with one of the following entries below the current date
7|12
7
8
9
10
11
12
13
14
15
16
17
19|12

when employee is NOT at work is marked with one of the following entries or the cell is left blank below the current date
UA
BT
SL
UL
RL
PL
CD
TR
N/A


P.S. it should look like status cell infront of each employee name and it will be green if he is at work today and it will be red if he is not.

Use conditional formatting

for first scenario type in criteria =IsNumber(j5:AO6)
and set the format to Green

for the second scenario type in criteria =IsText(J5:AO6)
and set the format to Red.

Expand the criteria range as necessary.

Alan

Hi alansidman,

thank you for your suggestion.

you did not understand my question - i want to change the colors or at least enter certain value in the cell in front of each name of the employees depending if they are at work or if they are NOT. This is the cell column "B"

i have attached second example - i hope that this time its more clear what is my issue.

basically i want to open the schedule and quickly identify who is at work today and who is away.

this is similar to all chat software that shows if you are online - so i just want to see which employee should be at work today in front of their names, so if i see green cell in front of someones name this means that on current date in the schedule he has to be at work, if i see red cell in front of someones name this means that on current date in the schedule he is probably in paid leave or sick leave and etc.

see attachment

please i really need this formula, can someone at least guide me how to construct it...

Hi,

Could you post a before/ after since I would suggest the same thing that Alan suggested? From your example, if the its a number then they are working and if it's a string then they are off work that day. Conditional formatting is the easiest way to accomplish this. If this is not what you after then can you elaborate further.

abousetta

as i mentioned above - i want to recolor automatically only the cells in front of each name.
as an example if the name is in cell C8 - i want the cell B8 to be colored in RED or GREEN depending on the current status of the employe in the schedule under the current date column.

see example 3 attachment below.


also i apologize that I'm not able to express my issue clearly...

bump - anyone please help me find the exact formula

2nd bump - i need this formula pls

Hello godlev,

Try this in CF.

For GREEN,

=ISNUMBER(INDEX($K5:$AE5,MATCH(TODAY(),$K$2:$AI$2,0)))

For RED,

=1-ISNUMBER(INDEX($K5:$AE5,MATCH(TODAY(),$K$2:$AI$2,0)))

This formula works 99% of my requirements thank you for solving my issue!

i have one additional question...

as far as i can see the formula checks if there is number in the cell...

how can i modify the formula so it should count "TR" for GREEN?

Try this,

=INDEX($K5:$AE5,MATCH(TODAY(),$K$2:$AI$2,0))="TR"

wow Haseeb A - yo are true excel master thank you - this thread is marked as solved!