Formulas and Functions
Formulas and Functions
Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.
Nested IF Formula - with drop down list
Hello,
I'm trying to figure out how to create a really long IF formula based on options provided in a drop down list.
I'm having students select their major from a drop down list. Depending on what major(s) they select, I'd like to auto populate the division that that major is connected to in a different column.
For example...
Majors: Accounting, Business, Economics, Entrepreneurship
Division: Business
Majors: Nursing, Biology, Math, Automotive, Engineering
Division: Science & Technology
How would I create an IF statement based on long lists such as these with different true conditions?
Also, should I just separate each IF formula in a different column that represents each division and perhaps have it connected to a checkbox to indicate whether or not that's the division that this major falls under?
Thanks for your help!
Lauren
Best Answers
-
Hi Lauren,
How many Majors do you need to account for? Depending on the amount, I would recommend using a VLOOKUP or INDEX/MATCH combination.
A nested IF formula would look something like this.
=IF(OR(Majors@row = "Accounting"; Majors@row = "Business"; Majors@row = "Economics"; Majors@row = "Entrepreneurship"); "Business")
The same version but with the below changes for convenience.
=IF(OR(Majors@row = "Accounting", Majors@row = "Business", Majors@row = "Economics", Majors@row = "Entrepreneurship"), "Business")
Depending on your country/region, you'll need to exchange the comma to a period and the semi-colon to a comma.
I hope that helps!
Be safe and have a fantastic week!
Best,
Andrée Starå
Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E: andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
I also suggest creating a reference table and then using an INDEX/MATCH or VLOOKUP. That avoids having a massive IF that can be hard to manage if you have any changes, additions, and/or removals to make.
-
You're more than welcome!
To add. Regarding, INDEX/MATCH.
It's more secure because the formula can't be altered to show something else (in a VLOOKUP, you can change the column number to show different information). It's more robust because you can move columns and add columns, which won't affect the formula.
I hope that helps!
✅Remember! Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E: andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Answers
-
Hi Lauren,
How many Majors do you need to account for? Depending on the amount, I would recommend using a VLOOKUP or INDEX/MATCH combination.
A nested IF formula would look something like this.
=IF(OR(Majors@row = "Accounting"; Majors@row = "Business"; Majors@row = "Economics"; Majors@row = "Entrepreneurship"); "Business")
The same version but with the below changes for convenience.
=IF(OR(Majors@row = "Accounting", Majors@row = "Business", Majors@row = "Economics", Majors@row = "Entrepreneurship"), "Business")
Depending on your country/region, you'll need to exchange the comma to a period and the semi-colon to a comma.
I hope that helps!
Be safe and have a fantastic week!
Best,
Andrée Starå
Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E: andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
I also suggest creating a reference table and then using an INDEX/MATCH or VLOOKUP. That avoids having a massive IF that can be hard to manage if you have any changes, additions, and/or removals to make.
-
If I were to do a VLOOKUP or INDEX/MATCH, what is the difference between the two, and what would those formulas look like?
I'm looking back through other community posts to see if I can figure it out, but figured I'd just go ahead and ask here as well.
Thanks!
-
Hi there,
I'm clearly off somewhere...
Some of the columns are matching up, but some aren't. Below you can see the formula that I used to try and create the match.
Here is the reference sheet:
Essentially, I'd love if the major listed on the reference sheet was matched to the major on the form sheet (top image) then what would populate in the Meta Major column, would match the Meta Major column on the reference sheet.
I've adjusted the formula to end in 0, 1, -1 and it changes what appears.
0s show up as #No Match
1 or -1 give the wrong response - they don't end up providing the correct Meta Major, just a seemingly random on on the list.
-
The biggest difference between Vlookup and Index/Match is Vlookup depends on a consistent range or table of information. this uses a lot of cell references. Index Match only references the Index range (think what you want displayed) and the Match Range (what you are looking for to match). so index match only references two columns where Vlookup could require a large range of columns to get your answer depending on how you sheet is set up.
-
thanks everyone for your insights! I was able to adjust the formula (I had my source columns transposed) and it works now. I appreciate your quick feedback.
Lauren
-
You're more than welcome!
To add. Regarding, INDEX/MATCH.
It's more secure because the formula can't be altered to show something else (in a VLOOKUP, you can change the column number to show different information). It's more robust because you can move columns and add columns, which won't affect the formula.
I hope that helps!
✅Remember! Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E: andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Glad you were able to get it working, and happy to help! 👍️
-
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E: andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 67.9K Get Help
- 474 Global Discussions
- 207 Use Cases
- 517 Announcements
- 5.5K Ideas & Feature Requests
- 87 Brandfolder
- 157 Just for fun
- 83 Community Job Board
- 521 Show & Tell
- 36 Member Spotlight
- 3 SmartStories
- 309 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!