Find Instances of a Value in Another Sheet

Art Schneiderheinze

Hello. I have two sheets:

1. Sheet #1 is a list of programs at our university with the following columns (and sample values)
   1. Column "College" (e.g., PB, which is code for College of Psychological and Behavioral Sciences)
   2. Column "Program" (e.g., Applied Behavioral Sciences, B.A.)
   3. Column "Required" (e.g., ABS 400,ABS 401,ABS 305,ABS 300,ABS 340,ABS 309,ABS 331)
   4. Column "Electives" (e.g., ABS 435,LIBR 300)
2. Sheet #2 is intended for a user to enter a course code (e.g., ABC 123) and see a list of programs that have that course as a Required course or Elective course.

On Sheet #2, I'd like for the user to enter a course code (e.g., ABS 400), then in the rows below, list the colleges and programs for which that course is Required or Elective.

The results may look something like this:

USER ENTERS: ABS 400

The rows below would then parse the information into three columns, with each college/program match appear in its own row:

PB | Applied Behavioral Sciences, B.A. | Required

PB | Clinical Counseling (Florida) B.S. | Required

PB | Clinical Counseling (Illinois), B.S. | Elective

UC | Counseling (Concentration) | Elective

UC | Adult Learning (Concentration) | Elective

The number of rows may vary based on the course code, but there would be three columns of information displayed if the course code is a match in any college/program (on Sheet #1) OR it says "No Match" if the course code isn't a required or elective course in any program.

I can imagine it being an INDEX/MATCH/COLLECT/JOIN kind of formula, which might be used in a hidden column, then that collected information from Sheet #1 (if not blank, which would result in a "No Match"), would display in each subsequent row in Sheet #2 (separated perhaps wherever a comma appears, if a comma is used to join the matches).

Thanks!

Genevieve P.

Hi @Art Schneiderheinze

Is this the same question/scenario as your other post, here? Let me know if the solution on your other post resolves this, too!

The one thing I will note is that the JOIN(COLLECT formula can only join together text into one cell, it won't be able to parse out the information down different rows in your Sheet 2.

Cheers,

Genevieve