Need a formula to determine priority based on rank

Hello all,

New to the forum (lurking for a while). I have a spreadsheet that I could really use some help with. I'll try to explain in plain English and then attach a sample file that I am working with.

I have a data file from which data must be extracted to help prioritize and manage employee time to ensure they are completing their tasks based upon the earliest task end date. Each employee supports multiple companies at the same time, and as such their level of effort is defined based upon how their time must be split up. Once each task is complete and the hours have been spent the tasks are removed from the sheet, then the employee moves to the next task on the list.

My spreadsheet uses a rank formula to rank each employee's tasks based upon the end date.... On a separate tab there is an LOE reference list that defines what percent of time each employee is expected to spend on each customer. I am trying to determine a formula that will look up the LOE from the LOE reference tab and only populate a value in the LOE column on my worksheet if the employee should be working on that task right now. If the employee's LOE for the first task is not 100%, I need it to find the next task in line and populate the LOE for that tab so that they are 100% tasked at all times.

Here is a quick example of what I need:

David supports Company A,B,C, and D. Company D has a task ending on 6/15/17 which is the soonest end date therefore the rank is 1. David's expected level of effort for the company is 50%. Since David is not 100% allocated, I need a formula that will see that 50% of his time is still unallocated, look at the next lowest ranking number, and also assign the LOE percent to that column as well. If the formula sees that David is at 100% allocation, it should not tell him to start another project.

I haven't been able to figure out a formula that will take care of all of this so I am hoping that some of the excel experts can help me solve the problem. I have attached my example sheet in hopes that this will make sense. I appreciate all of the help in advance!