Find Instances of a Value in Another Sheet
Hello. I have two sheets:
- Sheet #1 is a list of programs at our university with the following columns (and sample values)
- Column "College" (e.g., PB, which is code for College of Psychological and Behavioral Sciences)
- Column "Program" (e.g., Applied Behavioral Sciences, B.A.)
- Column "Required" (e.g., ABS 400,ABS 401,ABS 305,ABS 300,ABS 340,ABS 309,ABS 331)
- Column "Electives" (e.g., ABS 435,LIBR 300)
- 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!
Answers
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!