IF/INDEX/MATCH?
I'm trying to create a formula for an on-boarding tracking sheet.
-There is one sheet for each new hire. The first row contains info about the new hire, including a column *Position*, which has a single-select dropdown with five position types: "Staff", "Academic Staff", Temp/contingent", "Postdoc" "Faculty".
-Remaining rows are lists of tasks.
-The *Required for Positions* column of the task rows is a multi-select drop down of positions for which that task may or may not be required: "Staff", "Academic Staff", Temp/contingent", "Postdoc" "Faculty".
-I would like to put a formula in the *Status* column that looks and the *Position* cell in Row 1: $[Position$]1 and checks it against the items selected in the *Required for Positions* column. If there is a match, *Status* should default to "Not Started". If there is no match, *Status* should default to "Not Required" (to which I then set up conditional formatting that provides a visual cue that this task is not required for this employee).
I have entered a formula into each of the *Status* cells but that is cumbersome and the formula would have to change each time the requirements change. Here's an example of a task that is required only for "Faculty" or "Staff" positions: =IF(OR($Position$1 = "Faculty", $Position$1 = "Staff", $Position$1 = "Academic Staff"), "Not started", "Not Required")
I know there's got to be better way but I haven't been able to find an example. Hoping one of you formula-gurus can help! Many thanks in advance! 🙂
Best Answer
-
Hi @Mary Ayers,
Since you're using a mutli-select column, you can use the HAS function to search the cell in each row to see if it has the same content as in your Position1 cell. Try this:
=IF(HAS([Required for Positions]@row, $Position$1), "Not started", "Not Required")
Let me know if this works for you!
Cheers,
Genevieve
Answers
-
Hi @Mary Ayers,
Since you're using a mutli-select column, you can use the HAS function to search the cell in each row to see if it has the same content as in your Position1 cell. Try this:
=IF(HAS([Required for Positions]@row, $Position$1), "Not started", "Not Required")
Let me know if this works for you!
Cheers,
Genevieve
-
Thank you, Genevieve! This worked perfectly! :)
Cheers,
Mary
-
Wonderful! I'm glad this worked for you 🙂
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!