Hello, i was thinking a formula on below request, but struggling on if statement or index+match
can anyone here help me out?
column A:M is my RDO(Rotation Day off) table for my employee
column O:P is their RDO# scheduled for everyone
column R:W is their weekly roaster.
question is, how can i simply apply "RDO"(U3:U35/W3:W35) for my easier reference if found column T / column V are blank cells and match the date(T1/V1) with this table(column A:M)?
the answer of 8/12/2019 & 9/12/2019 are RDO4 & RDO3 respectively will be correct.
ps. if RDO are correct but wrongly deployed schedule, i want to return wrong for my another reference
thanks for everyone's help.
can anyone here help me out?
column A:M is my RDO(Rotation Day off) table for my employee
column O:P is their RDO# scheduled for everyone
column R:W is their weekly roaster.
question is, how can i simply apply "RDO"(U3:U35/W3:W35) for my easier reference if found column T / column V are blank cells and match the date(T1/V1) with this table(column A:M)?
the answer of 8/12/2019 & 9/12/2019 are RDO4 & RDO3 respectively will be correct.
ps. if RDO are correct but wrongly deployed schedule, i want to return wrong for my another reference
thanks for everyone's help.
Book1 | |||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | |||
1 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | Bee | 7 | 8/12/2019 | 9/12/2019 | ||||||||||||||
2 | 3/1/2019 | 2/1/2019 | 1/1/2019 | 6/1/2019 | 5/1/2019 | 11/1/2019 | 4/1/2019 | Brenda | 2 | RDO # | SCHEDULE | REMARKS | SCHEDULE | REMARKS | |||||||||||
3 | 9/1/2019 | 8/1/2019 | 7/1/2019 | 12/1/2019 | 18/1/2019 | 17/1/2019 | 10/1/2019 | Brian | 1 | Bee | 7 | 10:00 - 19:30 | 11:30 - 21:00 | ||||||||||||
4 | 15/1/2019 | 14/1/2019 | 13/1/2019 | 25/1/2019 | 24/1/2019 | 23/1/2019 | 16/1/2019 | Carlie | 5 | Brenda | 2 | 09:15 - 18:45 | |||||||||||||
5 | 21/1/2019 | 20/1/2019 | 19/1/2019 | 31/1/2019 | 30/1/2019 | 29/1/2019 | 22/1/2019 | Dave | 3 | Brian | 1 | ||||||||||||||
6 | 27/1/2019 | 26/1/2019 | 1/2/2019 | 6/2/2019 | 5/2/2019 | 4/2/2019 | 28/1/2019 | Dickson | 1 | Carlie | 5 | 09:15 - 18:45 | 09:15 - 18:45 | ||||||||||||
7 | 2/2/2019 | 8/2/2019 | 7/2/2019 | 12/2/2019 | 11/2/2019 | 10/2/2019 | 3/2/2019 | Fanny | 2 | Dave | 3 | 09:15 - 18:45 | 11:30 - 21:00 | WRONG | |||||||||||
8 | 15/2/2019 | 14/2/2019 | 13/2/2019 | 18/2/2019 | 17/2/2019 | 16/2/2019 | 9/2/2019 | Gwenda | 1 | Dickson | 1 | 11:30 - 21:00 | |||||||||||||
9 | 21/2/2019 | 20/2/2019 | 19/2/2019 | 24/2/2019 | 23/2/2019 | 29/2/2019 | 22/2/2019 | Hannah | 6 | Fanny | 2 | 09:15 - 18:45 | |||||||||||||
10 | 27/2/2019 | 26/2/2019 | 25/2/2019 | 1/3/2019 | 7/3/2019 | 6/3/2019 | 28/2/2019 | Hiko | 6 | Gwenda | 1 | 11:30 - 21:00 | 09:15 - 18:45 | ||||||||||||
11 | 4/3/2019 | 3/3/2019 | 2/3/2019 | 14/3/2019 | 13/3/2019 | 12/3/2019 | 5/3/2019 | Ian | 4 | Hannah | 6 | 00:00 - 00:00 | Vacation Leave Day 1.0 | ||||||||||||
12 | 10/3/2019 | 9/3/2019 | 8/3/2019 | 20/3/2019 | 19/3/2019 | 18/3/2019 | 11/3/2019 | Jay | 2 | Hiko | 6 | 09:15 - 18:45 | 11:30 - 21:00 | ||||||||||||
13 | 16/3/2019 | 15/3/2019 | 21/3/2019 | 26/3/2019 | 25/3/2019 | 24/3/2019 | 17/3/2019 | Judy | 2 | Ian | 4 | RDO | 09:15 - 18:45 | ||||||||||||
14 | 22/3/2019 | 28/3/2019 | 27/3/2019 | 1/4/2019 | 31/3/2019 | 30/3/2019 | 23/3/2019 | Kali | 1 | Jay | 2 | 09:15 - 18:45 | |||||||||||||
15 | 4/4/2019 | 3/4/2019 | 2/4/2019 | 7/4/2019 | 6/4/2019 | 5/4/2019 | 29/3/2019 | Kary | 4 | Judy | 2 | 09:15 - 18:45 | |||||||||||||
16 | 10/4/2019 | 9/4/2019 | 8/4/2019 | 13/4/2019 | 12/4/2019 | 18/4/2019 | 11/4/2019 | Keith | 5 | Kali | 1 | 09:15 - 18:45 | |||||||||||||
17 | 16/4/2019 | 15/4/2019 | 14/4/2019 | 19/4/2019 | 25/4/2019 | 24/4/2019 | 17/4/2019 | Kelly | 6 | Kary | 4 | 09:15 - 18:45 | WRONG | 09:15 - 18:45 | |||||||||||
18 | 22/4/2019 | 21/4/2019 | 20/4/2019 | 2/5/2019 | 1/5/2019 | 30/4/2019 | 23/4/2019 | Kelvin | 2 | Keith | 5 | 11:30 - 21:00 | |||||||||||||
19 | 28/4/2019 | 27/4/2019 | 26/4/2019 | 8/5/2019 | 7/5/2019 | 6/5/2019 | 29/4/2019 | Kevin | 5 | Kelly | 6 | 09:15 - 18:45 | |||||||||||||
20 | 4/5/2019 | 3/5/2019 | 9/5/2019 | 14/5/2019 | 13/5/2019 | 12/5/2019 | 5/5/2019 | Kin | 3 | Kelvin | 2 | 11:30 - 21:00 | |||||||||||||
21 | 10/5/2019 | 16/5/2019 | 15/5/2019 | 20/5/2019 | 19/5/2019 | 18/5/2019 | 11/5/2019 | Koey | 7 | Kevin | 5 | 11:30 - 21:00 | |||||||||||||
22 | 23/5/2019 | 22/5/2019 | 21/5/2019 | 26/5/2019 | 25/5/2019 | 24/5/2019 | 17/5/2019 | Ling | 3 | Kin | 3 | RDO | |||||||||||||
23 | 29/5/2019 | 28/5/2019 | 27/5/2019 | 1/6/2019 | 31/5/2019 | 6/6/2019 | 30/5/2019 | Luna | 4 | Koey | 7 | 11:30 - 21:00 | 11:30 - 21:00 | ||||||||||||
24 | 4/6/2019 | 3/6/2019 | 2/6/2019 | 7/6/2019 | 13/6/2019 | 12/6/2019 | 5/6/2019 | Molly | 4 | Ling | 3 | 11:30 - 21:00 | 11:30 - 21:00 | WRONG | |||||||||||
25 | 10/6/2019 | 9/6/2019 | 8/6/2019 | 20/6/2019 | 19/6/2019 | 18/6/2019 | 11/6/2019 | Moon | 5 | Luna | 4 | 09:15 - 18:45 | WRONG | 09:15 - 18:45 | |||||||||||
26 | 16/6/2019 | 15/6/2019 | 14/6/2019 | 26/6/2019 | 25/6/2019 | 24/6/2019 | 17/6/2019 | Phoebe | 6 | Molly | 4 | RDO | 00:00 - 00:00 | Vacation Leave Day 1.0 | |||||||||||
27 | 22/6/2019 | 21/6/2019 | 27/6/2019 | 2/7/2019 | 1/7/2019 | 30/6/2019 | 23/6/2019 | Pizza | 6 | Moon | 5 | 09:15 - 18:45 | 09:15 - 18:45 | ||||||||||||
28 | 28/6/2019 | 4/7/2019 | 3/7/2019 | 8/7/2019 | 7/7/2019 | 6/7/2019 | 29/6/2019 | Sam | 3 | Phoebe | 6 | 00:00 - 00:00 | Vacation Leave Day 1.0 | 00:00 - 00:00 | Vacation Leave Day 1.0 | ||||||||||
29 | 11/7/2019 | 10/7/2019 | 9/7/2019 | 14/7/2019 | 13/7/2019 | 12/7/2019 | 5/7/2019 | Suet | 3 | Pizza | 6 | ||||||||||||||
30 | 17/7/2019 | 16/7/2019 | 15/7/2019 | 20/7/2019 | 19/7/2019 | 25/7/2019 | 18/7/2019 | Tracy | 7 | Sam | 3 | 09:15 - 18:45 | RDO | ||||||||||||
31 | 23/7/2019 | 22/7/2019 | 21/7/2019 | 26/7/2019 | 1/8/2019 | 31/7/2019 | 24/7/2019 | Tyler | 3 | Suet | 3 | 09:15 - 18:45 | RDO | ||||||||||||
32 | 29/7/2019 | 28/7/2019 | 27/7/2019 | 8/8/2019 | 7/8/2019 | 6/8/2019 | 30/7/2019 | Yoka | 4 | Tracy | 7 | 11:30 - 21:00 | 09:15 - 18:45 | ||||||||||||
33 | 4/8/2019 | 3/8/2019 | 2/8/2019 | 14/8/2019 | 13/8/2019 | 12/8/2019 | 5/8/2019 | Zoe | 1 | Tyler | 3 | 11:30 - 21:00 | RDO | ||||||||||||
34 | 10/8/2019 | 9/8/2019 | 15/8/2019 | 20/8/2019 | 19/8/2019 | 18/8/2019 | 11/8/2019 | Yoka | 4 | RDO | 10:00 - 19:30 | ||||||||||||||
35 | 16/8/2019 | 22/8/2019 | 21/8/2019 | 26/8/2019 | 25/8/2019 | 24/8/2019 | 17/8/2019 | Zoe | 1 | 00:00 - 00:00 | Vacation Leave Day 1.0 | ||||||||||||||
36 | 29/8/2019 | 28/8/2019 | 27/8/2019 | 1/9/2019 | 31/8/2019 | 30/8/2019 | 23/8/2019 | ||||||||||||||||||
37 | 4/9/2019 | 3/9/2019 | 2/9/2019 | 7/9/2019 | 6/9/2019 | 12/9/2019 | 5/9/2019 | ||||||||||||||||||
38 | 10/9/2019 | 9/9/2019 | 8/9/2019 | 13/9/2019 | 19/9/2019 | 18/9/2019 | 11/9/2019 | ||||||||||||||||||
39 | 16/9/2019 | 15/9/2019 | 14/9/2019 | 26/9/2019 | 25/9/2019 | 24/9/2019 | 17/9/2019 | ||||||||||||||||||
40 | 22/9/2019 | 21/9/2019 | 20/9/2019 | 2/10/2019 | 1/10/2019 | 30/9/2019 | 23/9/2019 | ||||||||||||||||||
41 | 28/9/2019 | 27/9/2019 | 3/10/2019 | 8/10/2019 | 7/10/2019 | 6/10/2019 | 29/9/2019 | ||||||||||||||||||
42 | 4/10/2019 | 10/10/2019 | 9/10/2019 | 14/10/2019 | 13/10/2019 | 12/10/2019 | 5/10/2019 | ||||||||||||||||||
43 | 17/10/2019 | 16/10/2019 | 15/10/2019 | 20/10/2019 | 19/10/2019 | 18/10/2019 | 11/10/2019 | ||||||||||||||||||
44 | 23/10/2019 | 22/10/2019 | 21/10/2019 | 26/10/2019 | 25/10/2019 | 31/10/2019 | 24/10/2019 | ||||||||||||||||||
45 | 29/10/2019 | 28/10/2019 | 27/10/2019 | 1/11/2019 | 7/11/2019 | 6/11/2019 | 30/10/2019 | ||||||||||||||||||
46 | 4/11/2019 | 3/11/2019 | 2/11/2019 | 14/11/2019 | 13/11/2019 | 12/11/2019 | 5/11/2019 | ||||||||||||||||||
47 | 10/11/2019 | 9/11/2019 | 8/11/2019 | 20/11/2019 | 19/11/2019 | 18/11/2019 | 11/11/2019 | ||||||||||||||||||
48 | 16/11/2019 | 15/11/2019 | 21/11/2019 | 26/11/2019 | 25/11/2019 | 24/11/2019 | 17/11/2019 | ||||||||||||||||||
49 | 22/11/2019 | 28/11/2019 | 27/11/2019 | 2/12/2019 | 1/12/2019 | 30/11/2019 | 23/11/2019 | ||||||||||||||||||
50 | 5/12/2019 | 4/12/2019 | 3/12/2019 | 8/12/2019 | 7/12/2019 | 6/12/2019 | 29/11/2019 | ||||||||||||||||||
51 | 11/12/2019 | 10/12/2019 | 9/12/2019 | 14/12/2019 | 13/12/2019 | 19/12/2019 | 12/12/2019 | ||||||||||||||||||
52 | 17/12/2019 | 16/12/2019 | 15/12/2019 | 20/12/2019 | 26/12/2019 | 25/12/2019 | 18/12/2019 | ||||||||||||||||||
53 | 23/12/2019 | 22/12/2019 | 21/12/2019 | 31/12/2019 | 24/12/2019 | ||||||||||||||||||||
54 | 29/12/2019 | 28/12/2019 | 27/12/2019 | 30/12/2019 | |||||||||||||||||||||
RDO |
Cell Formulas | ||
---|---|---|
Range | Formula | |
S3:S35 | S3 | =VLOOKUP(R3,O:P,2,FALSE) |