RDO with formulas, if statement/index+match

kelvin_9

Active Member
Joined
Mar 6, 2015
Messages
444
Office Version
  1. 2019
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.


Book1
ABCDEFGHIJKLMNOPQRSTUVW
11234567Bee78/12/20199/12/2019
23/1/20192/1/20191/1/20196/1/20195/1/201911/1/20194/1/2019Brenda2RDO #SCHEDULEREMARKSSCHEDULEREMARKS
39/1/20198/1/20197/1/201912/1/201918/1/201917/1/201910/1/2019Brian1Bee710:00 - 19:3011:30 - 21:00
415/1/201914/1/201913/1/201925/1/201924/1/201923/1/201916/1/2019Carlie5Brenda209:15 - 18:45
521/1/201920/1/201919/1/201931/1/201930/1/201929/1/201922/1/2019Dave3Brian1
627/1/201926/1/20191/2/20196/2/20195/2/20194/2/201928/1/2019Dickson1Carlie509:15 - 18:4509:15 - 18:45
72/2/20198/2/20197/2/201912/2/201911/2/201910/2/20193/2/2019Fanny2Dave309:15 - 18:4511:30 - 21:00WRONG
815/2/201914/2/201913/2/201918/2/201917/2/201916/2/20199/2/2019Gwenda1Dickson111:30 - 21:00
921/2/201920/2/201919/2/201924/2/201923/2/201929/2/201922/2/2019Hannah6Fanny209:15 - 18:45
1027/2/201926/2/201925/2/20191/3/20197/3/20196/3/201928/2/2019Hiko6Gwenda111:30 - 21:0009:15 - 18:45
114/3/20193/3/20192/3/201914/3/201913/3/201912/3/20195/3/2019Ian4Hannah600:00 - 00:00Vacation Leave Day 1.0
1210/3/20199/3/20198/3/201920/3/201919/3/201918/3/201911/3/2019Jay2Hiko609:15 - 18:4511:30 - 21:00
1316/3/201915/3/201921/3/201926/3/201925/3/201924/3/201917/3/2019Judy2Ian4RDO09:15 - 18:45
1422/3/201928/3/201927/3/20191/4/201931/3/201930/3/201923/3/2019Kali1Jay209:15 - 18:45
154/4/20193/4/20192/4/20197/4/20196/4/20195/4/201929/3/2019Kary4Judy209:15 - 18:45
1610/4/20199/4/20198/4/201913/4/201912/4/201918/4/201911/4/2019Keith5Kali109:15 - 18:45
1716/4/201915/4/201914/4/201919/4/201925/4/201924/4/201917/4/2019Kelly6Kary409:15 - 18:45WRONG09:15 - 18:45
1822/4/201921/4/201920/4/20192/5/20191/5/201930/4/201923/4/2019Kelvin2Keith511:30 - 21:00
1928/4/201927/4/201926/4/20198/5/20197/5/20196/5/201929/4/2019Kevin5Kelly609:15 - 18:45
204/5/20193/5/20199/5/201914/5/201913/5/201912/5/20195/5/2019Kin3Kelvin211:30 - 21:00
2110/5/201916/5/201915/5/201920/5/201919/5/201918/5/201911/5/2019Koey7Kevin511:30 - 21:00
2223/5/201922/5/201921/5/201926/5/201925/5/201924/5/201917/5/2019Ling3Kin3RDO
2329/5/201928/5/201927/5/20191/6/201931/5/20196/6/201930/5/2019Luna4Koey711:30 - 21:0011:30 - 21:00
244/6/20193/6/20192/6/20197/6/201913/6/201912/6/20195/6/2019Molly4Ling311:30 - 21:0011:30 - 21:00WRONG
2510/6/20199/6/20198/6/201920/6/201919/6/201918/6/201911/6/2019Moon5Luna409:15 - 18:45WRONG09:15 - 18:45
2616/6/201915/6/201914/6/201926/6/201925/6/201924/6/201917/6/2019Phoebe6Molly4RDO00:00 - 00:00Vacation Leave Day 1.0
2722/6/201921/6/201927/6/20192/7/20191/7/201930/6/201923/6/2019Pizza6Moon509:15 - 18:4509:15 - 18:45
2828/6/20194/7/20193/7/20198/7/20197/7/20196/7/201929/6/2019Sam3Phoebe600:00 - 00:00Vacation Leave Day 1.000:00 - 00:00Vacation Leave Day 1.0
2911/7/201910/7/20199/7/201914/7/201913/7/201912/7/20195/7/2019Suet3Pizza6
3017/7/201916/7/201915/7/201920/7/201919/7/201925/7/201918/7/2019Tracy7Sam309:15 - 18:45RDO
3123/7/201922/7/201921/7/201926/7/20191/8/201931/7/201924/7/2019Tyler3Suet309:15 - 18:45RDO
3229/7/201928/7/201927/7/20198/8/20197/8/20196/8/201930/7/2019Yoka4Tracy711:30 - 21:0009:15 - 18:45
334/8/20193/8/20192/8/201914/8/201913/8/201912/8/20195/8/2019Zoe1Tyler311:30 - 21:00RDO
3410/8/20199/8/201915/8/201920/8/201919/8/201918/8/201911/8/2019Yoka4RDO10:00 - 19:30
3516/8/201922/8/201921/8/201926/8/201925/8/201924/8/201917/8/2019Zoe100:00 - 00:00Vacation Leave Day 1.0
3629/8/201928/8/201927/8/20191/9/201931/8/201930/8/201923/8/2019
374/9/20193/9/20192/9/20197/9/20196/9/201912/9/20195/9/2019
3810/9/20199/9/20198/9/201913/9/201919/9/201918/9/201911/9/2019
3916/9/201915/9/201914/9/201926/9/201925/9/201924/9/201917/9/2019
4022/9/201921/9/201920/9/20192/10/20191/10/201930/9/201923/9/2019
4128/9/201927/9/20193/10/20198/10/20197/10/20196/10/201929/9/2019
424/10/201910/10/20199/10/201914/10/201913/10/201912/10/20195/10/2019
4317/10/201916/10/201915/10/201920/10/201919/10/201918/10/201911/10/2019
4423/10/201922/10/201921/10/201926/10/201925/10/201931/10/201924/10/2019
4529/10/201928/10/201927/10/20191/11/20197/11/20196/11/201930/10/2019
464/11/20193/11/20192/11/201914/11/201913/11/201912/11/20195/11/2019
4710/11/20199/11/20198/11/201920/11/201919/11/201918/11/201911/11/2019
4816/11/201915/11/201921/11/201926/11/201925/11/201924/11/201917/11/2019
4922/11/201928/11/201927/11/20192/12/20191/12/201930/11/201923/11/2019
505/12/20194/12/20193/12/20198/12/20197/12/20196/12/201929/11/2019
5111/12/201910/12/20199/12/201914/12/201913/12/201919/12/201912/12/2019
5217/12/201916/12/201915/12/201920/12/201926/12/201925/12/201918/12/2019
5323/12/201922/12/201921/12/201931/12/201924/12/2019
5429/12/201928/12/201927/12/201930/12/2019
RDO
Cell Formulas
RangeFormula
S3:S35S3=VLOOKUP(R3,O:P,2,FALSE)
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top