Nested IF Formula - with drop down list


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!



Best Answers


  • 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.


  • 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.

  • RossL
    RossL ✭✭✭✭✭✭

    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.


  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    @Lauren Ford


    Glad you got it working!


    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: | E:[email protected] | 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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!