CODEWITHSUNDEEP
exceldrop-down-menuformula
RazorAlliance192
RazorAlliance192

Reputation: 752

Excel dropdown list values based on differt value in table

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

Upvotes: 0

Views: 63

Answers (1)

BruceWayne
BruceWayne

Reputation: 23283

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:

enter image description here

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))),"")

Upvotes: 1

Related Questions