Forum Discussion
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...
bmckenna44 Try using XMATCH with multiple criteria as follows:
=IF(ISNUMBER(XMATCH(1, (C2='20409'!P:P)*(D2='20409'!R:R))), OFFSET('20409'!$L$1, XMATCH(1, (C2='20409'!P:P)*(D2='20409'!R:R))-1, 0, COUNTIFS('20409'!P:P, C2, '20409'!R:R, D2)), '20409'!L:L)
For more information on this method, check out: https://exceljet.net/formulas/xmatch-with-multiple-criteria
Having said that, this method will perform poorly when referencing entire sheet columns. Since the sample file you've provided is using Excel tables, you can improve performance by using structured table references instead. However, Data Validation does not recognize structured table references directly. To get around this problem, you can either define names in Name Manager for the necessary columns in the table, or you can use the INDIRECT function as follows:
=LET(st, INDIRECT("choose_city_table[state_name]"), co, INDIRECT("choose_city_table[county_name]"), ci, INDIRECT("choose_city_table[city]"), IF(ISNUMBER(XMATCH(1,(C2=st)*(D2=co))), OFFSET(ci, XMATCH(1,(C2=st)*(D2=co))-1, 0, COUNTIFS(st, C2, co, D2)), ci))
Note: the above-mentioned formula is 255 characters long, which is the maximum length accepted by Data Validation.
Please see the modified example workbook (attached).
- even using XMATCH, the data validation is not retained after saving and reopening. The formula is still there, but I have to open data validation again and hit okay for it to start working...
bmckenna44 That's unfortunate indeed. What version of Excel are you using? Excel for the web? Did you try my modified version of the complete formula? Your original formula contains some logic errors, using AND with ISNUMBER/MATCH on each column separately, then using MATCH with column concatenation for the final output. Plus, there is an error in the OFFSET logic... the row number returned by MATCH needs to be adjusted by -1.
As mentioned in my previous reply, it's highly inefficient to reference entire sheet columns. You'd be better off defining named ranges for each of the applicable table columns in Name Manager. For example, "city", "county" and "state" as show below:
Name Manager: defined names for table columns
Then, use the defined names in the final Data Validation formula as follows:
=IF(ISNUMBER(XMATCH(1, (C2=state)*(D2=county))), OFFSET(city, XMATCH(1, (C2=state)*(D2=county))-1, 0, COUNTIFS(state, C2, county, D2)), city)Note: your table contains a record where the state, county and city names are blank. As such, the above formula will return an empty string, rather than the entire city list, if both C2 and D2 are blank, because a match was in fact found.
I hope this helps. Best of luck!
- I set defined names and used that Data Validation formula. But since state and county are empty to start upon opening this file, even after making the other selections, once I get to city and press the dropdown excel crashes.
