Searching within collected values from across multiple sheets

MarkLED

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:

=IF(CONTAINS(

[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.

Paul Newcome

You would use an INDEX/MATCH to pull the Path from the Course (reference sheet 3). Then you would nest that in a COUNTIFS referencing the second sheet to say that IF the COUNTIFS of the path/email combo is greater than or equal to 1, check the box.

=IF(COUNTIFS({Sheet 2 Path}, @cell = IFERROR(INDEX({Sheet 3 Path}, MATCH([Course Title]@row, {Sheet 3 Course}, 0)), "NA"), {Sheet 2 Email}, @cell = [User Email]@row)>= 1, 1)

Paul Newcome

Are you able to provide screenshots for context?

MarkLED

Hi Paul,

I've attached the screenshots here with the column I am trying to add a formula to.

For each row in Sheet 1 (Raw User Activity) I need to check that the Course is in a Learning Path that is associated with that user (identified by their email address).

Sheet 2 maps the user to their Learning Paths.

Sheet 3 maps the courses to the Learning Paths.

Can't seem to find a way to do this efficiently in one column.

Paul Newcome

You would use an INDEX/MATCH to pull the Path from the Course (reference sheet 3). Then you would nest that in a COUNTIFS referencing the second sheet to say that IF the COUNTIFS of the path/email combo is greater than or equal to 1, check the box.

=IF(COUNTIFS({Sheet 2 Path}, @cell = IFERROR(INDEX({Sheet 3 Path}, MATCH([Course Title]@row, {Sheet 3 Course}, 0)), "NA"), {Sheet 2 Email}, @cell = [User Email]@row)>= 1, 1)

MarkLED

Amazing, thank you Paul!

Paul Newcome

Happy to help. 👍️