Excel Defined Name List in Index dropdown

Question

I have cells that list the following

   **MyList**
1             green
2             blue
3             red
4             yellow
5             special
6             special

And I have a Specials list defined as special

   **Special**
apple
banana
grapes

I have the a formula that looks for a match and displays its value in a drop down so if 2 is listed in the left-most column blue would be the only drop down selection.

=INDEX($A$15:$B$20,MATCH($E20,$A$15:$A$20,0),2)

But the drop down for cells with 5 and 6 say 'special' it does not drop down the defined list name(special) contents.

How can I include a defined name in this formula?

Unknown Unknown · Accepted Answer · 2016-08-29T00:38:49

Set your lookup table to more closely represent the following:

Base the F20 Data Validation List's Source: on the following formula,

=OFFSET(INDEX($B$15:$B$20, MATCH(E20, $A$15:$A$20, 0)), 0, 0, 1, MATCH("zzz", INDEX($B$15:$D$20, MATCH(E20, $A$15:$A$20, 0), 0)))

The dynamic list in F20 should follow the table lookup from the value in E20.

If you are running out of room (C19:D20) then you may have to relocate the list altogether.

Excel Defined Name List in Index dropdown

1 Answers