Excel dropdown list values based on differt value in table

Question

I have the following table:

    A            B
1 Fruit        Apple
2 Fruit        Banana
3 Meat         Steak
4 Vegetable    Carrot
5 Meat         Hamburger
6 Fruit        Orange

I wish to create a dropdown list of the values in B grouped by the unique values in A. I already managed to create a dropdown list with the unique values of A, so if a user selects one of the values in the dropdown of A, I wish to get the have its corresponding values from B. Example:

    X         Y
1 Fruit     +Apple
            +Banana
            +Orange

2 Meat      +Steak
            +Hamburger

BruceWayne · Accepted Answer · 2017-01-09 18:17:59Z

1

You can use an Index/Small formula to get what you're looking for. Having the data validations in one column, with the results in another, is a little tricky, since the "B" range will be dynamic. However, this should give you a good starting place.

If your Data Validation choice is in A1, you can enter this (and drag down) in B1:

=INDEX($F$1:$F$6,SMALL(IF($E$1:$E$6=$A$1,ROW($F$1:$F$6)-ROW($F$1)+1),ROWS(F$1:F1)))

Enter that with CTRL+SHIFT+ENTER

Note you'll want to change your ranges to match where the data is. This is how it looks/works on my sheet:

And to hide that #NUM error, just wrap =IfError([formula],"") around it: =IfError(INDEX($F$1:$F$6,SMALL(IF($E$1:$E$6=$A$1,ROW($F$1:$F$6)-ROW($F$1)+1),ROWS(F$1:F1))),"")

answered Jan 9, 2017 at 18:17

BruceWayne

23.5k16 gold badges75 silver badges126 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel dropdown list values based on differt value in table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related