How to set up dependent dropdown lists in MS Excel using OFFSET and SUMPRODUCT

In this article, we’ll demonstrate a simple approach to setting up a dropdown that depends on another dropdown. For example, we select a country in cell F1 and this changes the list of cities available for selection in cell F2, as shown in Figure 1.

Figure 1. Selecting a city in a country
Figure 1. Selecting a city in a country

Let’s assume we have already set up a country dropdown list referring to the range A1:C1, then we can set up a list of cities using the formula below where:

  • OFFSET returns the range for the dependent dropdown

  • A2 fixes the starting cell for the OFFSET function

  • 0 tells the OFFSET function that there is no vertical offset

  • MATCH(F1;A1:C1;0)-1 tells the OFFSET function how many columns to move to the right from the starting cell A2

  • SUMPRODUCT((F1=A1:C1)*(A2:C3<>“”)) tells the OFFSET function the number of nonblank cells (A2:C3<>“”) in the selected column (F1=A1:C1)


Figure 2 shows a dependent list of cities when Ukraine is selected in the country cell, where:

  • (F1=A1:C1) is an array {FALSE;TRUE;FALSE}

  • (A2:C3<>“”) is an array {FALSE;TRUE;TRUE:FALSE;FALSE;TRUE}

  • (F1=A1:C1)*(A2:C3<>“”) is an array {0;1;0:0;0;0} because the product FALSE*FALSE or FALSE*TRUE is 0 while the product is TRUE *TRUE is 1

  • The SUMPRODUCT function returns the sum of the array {0;1;0:0;0;0}, equal to 1 in our case

Figure 2. Choosing a city in Ukraine
Figure 2. Choosing a city in Ukraine

The demonstrated approach for setting dependent drop-down lists is the simplest and most intuitive of all.

Similar Posts

Leave a Reply