Data Validation Cascading dropdown lists

bmckenna44 After additional testing, I was finally able to reproduce the problem on my system. It seems to occur when the lookup_array parameter of the MATCH function (or XMATCH) is constructed directly in the Data Validation source formula. One solution that appears to work is to define a name for the lookup array in Name Manager by concatenating the two lookup columns. For example, define a new name called "state_county" with the following formula:

=choose_city_table[state_name]&"|"&choose_city_table[county_name]

Name Manager: with additional lookup array

Then, modify the Data Validation formula as follows:

=IF(COUNTIFS(state, C2, county, D2)>0, OFFSET(city, XMATCH(C2&"|"&D2, state_county)-1, 0, COUNTIFS(state, C2, county, D2)), city)

Note: I tested this method with Office 2010 (using MATCH), and it worked properly when closing/reopening the file.

If the LET function is available in your version of Excel, you could also simplify the formula as follows:

=LET(h, COUNTIFS(state, C2, county, D2), IF(h>0, OFFSET(city, XMATCH(C2&"|"&D2, state_county)-1, 0, h), city))

Furthermore, instead of using OFFSET with XMATCH and COUNTIFS (as well as IF with ISNUMBER and XMATCH), you could create an XLOOKUP based LAMBDA function in Name Manager called "XLIST" with the following formula:

=LAMBDA(lookup_value,lookup_array,return_array,
   XLOOKUP(lookup_value, lookup_array, return_array, INDEX(return_array, 1)):
   XLOOKUP(lookup_value, lookup_array, return_array, INDEX(return_array, ROWS(return_array)),, -1)
)

Then, use the XLIST function as the Data Validation source:

=XLIST(C2&"|"&D2, state_county, city)

Note: this method can be used for every level of your dependent dropdown lists.

Please see the attached workbooks for examples of the above-mentioned methods...