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.

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)

=OFFSET(A2,0,MATCH(F1,A1:C1,0)-1,SUMPRODUCT((F1=A1:C1)*(A2:C3<>“”)))

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