Searching within collected values from across multiple sheets

I am having trouble coming up with a way to search within values using a Collect formula.

I have three sheets, one which shows the courses a user has completed, one which shows the courses included in each learning plan {CourseMapping_Course} {CourseMapping_Paths}, and one which shows the learning paths assigned to each user {UserMapping_email} {UserMapping_paths}.

The challenge is to add a column in the first sheet to mark if the completed course is part of the user's assigned learning plan. I need to be able to do this in one column due to how we import this data.

What I have attempted so far is nesting Collects with Match formulas as so:


[Course Title]@row,

(COLLECT({CourseMapping_Course}, {CourseMapping_Paths}, MATCH({UserMapping_Paths},

(COLLECT({UserMapping_Paths}, {UserMapping_email}, MATCH([User Email]@row, {UserMapping_email}))))))), 1, 0)

The logic is that I want to take the user's email and use this to find what learning paths they are mapped to, then take the name of the course they have completed and see if it is in one of their assigned learning plans.

Index does not seem useful here as the users can be assigned to multiple learning paths, and each course may also exist in multiple learning paths.

Is it possible to use a COLLECT in this way? If not, is there a different function which would make the referencing of three tables easier.

Best Answer