Nested IF Formula - with drop down list

Lauren Ford

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

Andrée Starå

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!

Paul Newcome

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.

Andrée Starå

@Lauren Ford

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!

Andrée Starå

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!

Paul Newcome

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.

Lauren Ford

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!

Lauren Ford

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

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.

Lauren Ford

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

Andrée Starå

@Lauren Ford

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!

Paul Newcome

Glad you were able to get it working, and happy to help! 👍️

Andrée Starå

@Lauren Ford

Excellent!

Glad you got it working!