Excel: IFS or Nested If Statements

Question

I'm having an issue writing an IFS statement in Excel.

This is what I would like to happen: User chooses from a list of 8 names in B1. Based on the selection of B1, in B2 the user will get a dropdown of a more refined list to choose from.

This is how I have the IFS statement written so far, which is causing trouble:

=IFS(B1="A",A),["B",B],["C",C],["D",D],["E",E],["F",F],["G",G],["H",H])

I have also written this which doesn't work either:

=IFS(B1="A",A),[B1="B",B],[B1="C",C],[B1="D",D],[B1="E",E],[B1="F",F],[B1="G",G],[B1="H",H])

I keep getting error messages.

Does anyone have any suggestions?

Thank you!

Answer 1

The common way is with dependant drop down lists. Very easy.

Consider a simplified example.

You have 8 initial options A-H. This will be the source list for your first data validation drop down. Then for each of the selections in that list you will create a named range of the same name as the selection.

So named range called A, B etc.

Then for the second drop down data validation you put List and as Source you put Indirect(cell containing first data validation list)

So in the example below:

The initial drop down list is in C1 and its source is column E

You can see in the top left name manager box that the highlighted range in column G is named as A.

The second drop down cell D1 has the source formula =INDIRECT($C$1)

So when A is selected in C1 then data validation in D1 comes from Indirect to named range A which is the list in column G.

Answer 2

In such situations, you can also use a combination of choose and match. It is more readable and easier to maintain:

=CHOOSE(MATCH(B1,{"A","B","C","D","E","F","G"},0), "Action A", "Action B", "Action C", "Action D", "Action E", "Action F", "Action G")

It first matches user's choice with a list of options (you can also specify the list as a range of cells in your sheet). Then it chooses the action based on that selection.