How do I return a value of a cell based on a condition?
I am looking to return a value of a semester (i.e. Spring2020) from a particular cell in another sheet, for a particular student ID (i.e. 55555) taking a particular course (i.e. 6000)
I used this formula to count how many courses student is taking, and that gives me a number
=COUNTIFS({coursenumber}, "6000", {studentid}, "5555")
I need something like this
=IF(CONTAINS({coursenumber},"6000",{studentid},"5555"),{semester})
Answers

Hi @gwson
You could use a JOIN(COLLECT formula for this, even though you don't have values to Join (you'll just be returning one cell, assuming that only one value meets all the criteria).
Try this:
=JOIN(COLLECT({Semester Range}, {Course Number}, [Course Number]@row, {Student ID}, [Student ID]@row))
This is assuming you have a sheet with the Course Number and Student ID listed, so you don't have to create a new formula for each individual student and course. Otherwise one example formula would be as follows:
=JOIN(COLLECT({Semester Range}, {Course Number}, "6000" {Student ID}, "5555"))
Let me know if this works for you!
Genevieve
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.1K Get Help
 380 Global Discussions
 212 Industry Talk
 443 Announcements
 4.6K Ideas & Feature Requests
 140 Brandfolder
 129 Just for fun
 130 Community Job Board
 450 Show & Tell
 30 Member Spotlight
 1 SmartStories
 290 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!