Lookup function for a grade book to pull multiple values for one person

Hello everyone! Sorry for how wordy this is.

I can download a copy of a gradebook from my software to import to Excel. Each student is on a separate line, each assignment is a separate column. If the assignment has been graded, there is a grade. If not, it is blank. The students do not start at the same time, but I do get a column that shows how many weeks they have been in the class.

During their first four weeks, I would like to email them each week to tell them which 3 assignments they should be working on that week, but if they have already completed one or more of those, I would like for them to see the next three assignments.

Without knowing how to approach this, I extended the table, and made a simple watching cell for each of the assignments in question for each student. If the grade book cell is blank, the column number of that assignment is displayed in the watching cell: =IF(ISBLANK(H3),COLUMN(H3)," "). If there is a grade, the watching cell is blank. I was thinking about using the column number with a Vlookup to pull the name of the assignments.

The flaw in my plan, is me not having the foggiest idea how to pull the first three non blank values from that range, for each student. For example, Johnny has the following values in the range of cells: blank, blank,7,8,10,12, etc. I want to pull the names that are the headers for column 7, column 8, and column 10. I figured that I can get the first one with something like =Vlookup("assignment",$A$2:$DZ$2,(something to pull that 7), false).

If I had to(with brute force and ignorance), I could hard code rules in my mail merge file that would just add the next two assignment names, and just hope that everyone was working in order. I would rather do it based upon the values. I suppose that I could concatenate the column numbers together, and have a huge nested if then formula for all of the possible strings that would make.

To make it more complicated, the have banned the use of macros on our machines. I am pretty sure that a for... next loop would have it taken care of in a few minutes. Does anyone have any ideas? I have started looking in the direction of arrays, but have not had sufficient time to dig through those functions yet. Any ideas would be greatly appreciated, even if they involve tossing all of the junk I have been playing with, and going in a completely different direction .

Thank you, BobR

---------- Post added at 11:22 PM ---------- Previous post was at 11:17 PM ----------

Even more complicated, my grade book contains people working in 2 different semesters, some on a "standard track" and some on an "honors track". The appropriate assignments for each of the 4 groups is different for each week. If I can find out how to do it once, I can do it for the other groups without a problem.

Here you go. I was too tired last night to put this together.

Based on the weeks active (red) the student should reach certain assignments. The orange assignments are the ones that I would like the titles for (yellow or blue).

fake.xlsx