Complex look up with output

Hi there,

I can see you’re looking to cross-reference a table of training courses with a report of students’ completed courses, filtering dynamically to identify which courses each student is missing for their specific grade and level. Here’s a structured way to think through this in Excel:

Step 1: Understand the Data Structure

To solve this problem, we’ll use three tables:

• tbl_courses: Contains the course names and their required grade and level.
• tbl_courses_completed: Contains each student’s name and the courses they’ve completed.
• tbl_current_students: Contains the current student list with their grade and level.

The goal here is to filter these tables to get a list of courses for each student that they still need to complete for their grade and level.

Step 2: Define the Lookup Process

For each student, you’ll want to:

1. Find their grade and level: Using the student’s name in tbl_current_students, we can look up their specific grade and level.
2. List Required Courses for the Grade and Level: Filter tbl_courses to retrieve the courses required for that grade and level.
3. Identify Courses Already Completed: Look up the student in tbl_courses_completed to find the courses they’ve finished.
4. Calculate Missing Courses: Compare the required courses with the completed courses and list any that are missing.

Step 3: Build the Formula

Here’s an outline of a potential formula approach:

1. Determine Grade and Level: Use XLOOKUP to find the student’s grade and level based on their name.
2. Get Required Courses: Use FILTER to list courses from tbl_courses that match the student’s grade and level.
3. Filter Out Completed Courses: With FILTER or MATCH, identify courses in this list that aren’t found in tbl_courses_completed for that student.
4. Display Missing Courses: If any courses remain, output them as a list for that student.

This approach could be set up in a third sheet with dropdown menus to select the student, grade, and level dynamically, displaying a list of any missing courses for easy review.  You could elect to use this formula on a case by case basis (change the name in the target cell and have the results displayed) or you could stack it together for use later (example at end).


Step 1: define the data structure

first table with course names and course levels is "tbl_courses" and it has two columns, "course_name" and "course_level".

second table with student names and completed courses is "tbl_courses_completed" and it has three columns, "student_name", "course_name", and "course_grade_level".

third table with current student names and grade level is "tbl_current_students" and it has two columns "name" and "grade_level".

Step 2. 1 - Find their grade and level:

current_student_grade = xlookup(student_name, tbl_current_students[name], tbl_current_students[grade_level])

Step 2.2 - List Required Courses for the Grade and Level

classes_for_current_student_grade = filter(tbl_classes[course_name],tbl_classes[grade_level]=current_student_grade),

Step 2.3 - Identify Courses Already Completed

current_student_grade_level_classes_taken =  filter(tbl_classes[course_name], (tbl_courses_completed[student_name]=current_student_name)*(tbl_courses_completed[course_grade_level]=current_student_grade))

Step 2.4 Calculate Missing Courses

has_taken_class_names_in_grade_boolean_mask = byrow(classes_for_current_grade, lambda(class_name, isnumber(match(current_class, current_student_grade_level_classes_taken,0)))

current_student_grade_level_classes_not_taken = filter(classes_for_current_grade, not(has_taken_class_names_in_grade_boolean), "all_classes_taken")

lambda form for finding missing classes for a particular student:

get_missing_classes = lambda(
    student_name,
    let(
        current_student_grade, 
            xlookup(
                student_name, 
                tbl_current_students[name], 
                tbl_current_students[grade_level]
            ),
        classes_for_current_student_grade, 
            filter(
                tbl_classes[course_name],
                tbl_classes[grade_level]=current_student_grade
        ),

        current_student_grade_level_classes_taken, 
            filter(
                tbl_classes[course_name], 
                (
                    (tbl_courses_completed[student_name]=current_student_name)*
                    (tbl_courses_completed[course_grade_level]=current_student_grade)
        ),

        has_taken_class_names_in_grade_boolean_mask, 
            byrow(
                classes_for_current_grade, 
                lambda(
                    class_name, 
                    isnumber(
                        match(
                            current_class, 
                            current_student_grade_level_classes_taken,
                            0
                        )
                    )
                )
        ),
        current_student_grade_level_classes_not_taken,
            filter(
                classes_for_current_grade, 
                not(has_taken_class_names_in_grade_boolean), 
                "all_classes_taken"
        ),
        current_student_grade_level_classes_not_taken
    )
);

Now let's say you want to use this formula to populate an easily referenced data set, one way to do that is to put it into a table where the student's name and grade are appended to each missing class name, thereby letting you filter by class, grade, or name, and create useful sets of data like, "grade 3 students who need algebra" by filtering the grade column for 3 and the class name column for algebra.

Here is one possible implementation:

=let(
    list_of_students, tbl_current_students[name],
    
    number_of_students, rows(list_of_students),
    list_of_student_indices, sequence(number_of_students),
    
    get_student_name, lambda(idx, index(list_of_students, idx, 1)),
    
    get_student_grade,
        lambda(
            student_name, 
            xlookup(
                student_name, 
                tbl_current_students[name], 
                tbl_current_students[grade_level]
            )
    ),
    
    get_missing_classes_for_student,
        lambda(
            current_student_name, 
            current_student_grade,
            let(
                classes_for_current_student_grade, 
                    filter(
                        tbl_courses[course_name],
                        tbl_courses[grade_level] = current_student_grade
                ),

                current_student_grade_level_classes_taken, 
                    filter(
                        tbl_courses_completed[course_name], 
                        (
                            (tbl_courses_completed[student_name] = current_student_name) *
                            (tbl_courses_completed[course_grade_level] = current_student_grade)
                        )
                ),

                has_taken_class_names_in_grade_boolean_mask, 
                    byrow(
                        classes_for_current_student_grade, 
                        lambda(
                            class_name, 
                            isnumber(
                                match(
                                    class_name, 
                                    current_student_grade_level_classes_taken,
                                    0
                                )
                            )
                        )
                ),
                
                current_student_grade_level_classes_not_taken,
                    filter(
                        classes_for_current_student_grade, 
                        not(has_taken_class_names_in_grade_boolean_mask), 
                        "all_classes_taken"
                ),
                
                current_student_grade_level_classes_not_taken
            )
    ),
    
    append_grade_and_name_to_missing_class_list,
        lambda(
            student_idx,
            let(
                student_name, get_student_name(student_idx),
                student_grade, get_student_grade(student_name),
                
                missing_classes, get_missing_classes_for_student(student_name, student_grade),
                
                hstack(
                    expand(student_name, rows(missing_classes), student_name),
                    expand(student_grade, rows(missing_classes), student_grade),
                    missing_classes
                )
            )
    ),
    
    get_current_student_missing_class_array,
        reduce(
            append_grade_and_name_to_missing_class_list(index(list_of_students, 1, 1)),
            drop(list_of_student_indices, 1), 
            lambda(
                acc,
                student_idx, 
                vstack(
                    acc,
                    append_grade_and_name_to_missing_class_list(student_idx)
                )
            )
    ),
    
    headers, hstack("Student Name", "Grade Level", "Missing Class"),
    
    result,
        vstack(
            headers,
            get_current_student_missing_class_array
        ),
    
    result
)

Let me know if any of this doesn't make sense or if there is an error somewhere.  I didn't test the formulas, so some minor tweaks may be required.

[Edit: When I initially posted this reply, I indicated that ChatGPT wrote the intro after a bit of prompting, but it seems that people might have understood that GPT wrote the whole post or designed the solution.  All ChatGPT did was make my initial narrative a little more friendly/accessible.  At this stage, it is utterly incapable of designing/crafting/effectively implementing the solution I provided.  I strongly caution you against taking ChatGPT's excel solutions at face value or assuming that they are optimized/ideal by any measure.]