Index Match + Data Validation

[SeveralTradesLater]

Hi everyone, new member here. After trying generative AI and being unable to source an answer. I hope a board of truly intelligent people can help me with this excel challenge. I am currently using Excel 2019. Please assist, any help would be greatly appreciated. Thank you.

I have the following excel function =IFNA(INDEX(DD_T,MATCH('FORM'!G12,D_L,0),2),"")

In my other sheet named "Form", when I user select a value from a list of drop-downs provided "ABC01,XYZ01,LMN01.etc" I want the user to be able to select from a data validation list of only associated values. If user selects "ABC01" in the data validation list they should only see a drop down of Apple, Car and Cup. If they select "XYZ01" they should only see Diamond or Paper from the dropdown list.

Generative AI provided the following solution, which does not work =IFNA(INDEX(DD_T,MATCH('FORM'!G12,D_L,0),2,MATCH('FORM'!G12,D_L,0)),"") It just results in a REF! error.

On Separate Sheet For Data Validation

Named as "DD_T" in Name Manager	Named as "D_L" in Name Manager
ABC01	APPLE
ABC01	CAR
ABC01	CUP
XYZ01	DIAMOND
XYZ01	PAPER
LMN01	VITAMINS
LMN01	COFFEE
APP01	CANDLE
NAP01	PHONE

 

[KRice]

Data Validation with lists satisfying certain conditions requires a couple steps. In your case, you will want a unique list of DD_T items so that your primary selection presents the user with a trimmed list that has no duplicates and ideally no blanks (to keep the dropdown list more compact). In Excel 365, an easy way to make that list relies on the UNIQUE function (see G3, which produces a list that spills down and whose length is limited by the number of unique items). In Excel 2019, you will need a different approach (UNIQUE is not available). I show one way to do that in some helper columns: see H3 where the formula is pulled down until blanks are produced. This ensures that all unique items have been found, but it also introduces blanks in the list, and if this list were to be referenced with Data Validation, the user would see several blanks appearing in the dropdown list. To clean this up (i.e., to trim the list to remove the blanks), the formula in I3 can be used, and it is this formula that is used in Data Validation to create the trimmed primary list for the first dropdown selection.

Then you will need a helper column to create a filtered list that shows only the D_L items associated with the chosen DD_T item. Again, in Excel 365, this would be easier with the FILTER function, but in Excel 2019, you will need a different approach. I show one way to do that in J3 where the formula is pulled down until blanks are produced (for the same reason as given above). Then to trim this list of blanks, the same approach is taken using the OFFSET function (in K3), which produces a trimmed list satisfying the match condition for use in Data Validation (and the Data Validation window uses this OFFSET formula).

Be careful with the OFFSET functions, as they both reference ranges that include blanks. If you have a large data set with longer lists of unique or matching items (columns H and J), then the ranges referenced in the formulas using OFFSET will need to be adjusted so that all relevant items appear in the final dropdown lists presented to the user. Ideally, you would build these lists (ulst_DD_T and flst_D_L) in a formal table and then you could use the column heading in the OFFSET formula...then you would not need to be concerned about adjusting the range references as that will be handled automatically.

One thing to be aware of...when you change the primary selection with the first dropdown (blue cell E2), the previously selected secondary selection (green cell E3) will remain and will be incorrect until the user performs a new selection using the newly re-formed dropdown list.

To summarize, the helper columns H and J are needed and can be placed anywhere (and hidden if desired). The columns using the OFFSET function are not necessary, but the formulas shown are used in the Data Validation setup.

MrExcel_20230530.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	DD_T	D_L					Excel 365	ulst_DD_T		flst_D_L
2	ABC01	APPLE		Select DD_T item	ABC01		Unique List for DD_T	Unique List for DD_T	Trimmed ulst_DD_T	Filtered List for D_L	Trimmed flist_D_L
3	ABC01	CAR		Selected D_L item	CAR		ABC01	ABC01	ABC01	APPLE	APPLE
4	ABC01	CUP					XYZ01	XYZ01	XYZ01	CAR	CAR
5	XYZ01	DIAMOND					LMN01	LMN01	LMN01	CUP	CUP
6	XYZ01	PAPER					APP01	APP01	APP01
7	LMN01	VITAMINS					NAP01	NAP01	NAP01
8	LMN01	COFFEE
9	APP01	CANDLE
10	NAP01	PHONE
11	XYZ01	CAN						drag down until blank	no blanks in list	drag down until blank	no blanks in list
SeveralTradesLater

Cell Formulas
Range	Formula
G3:G7	G3	=UNIQUE(DD_T)
I3:I7	I3	=OFFSET($H$3,,,MATCH(2,1/($H$3:$H$9<>"")))
K3:K5	K3	=OFFSET($J$3,,,MATCH(2,1/($J$3:$J$7<>"")))
J3:J7	J3	=IF(ROWS(J$3:J3)<=COUNTIF(DD_T,$E$2),INDEX(D_L,AGGREGATE(15,3,(ROW(DD_T)-ROW($A$1))/(DD_T=$E$2),ROWS($J$3:J3))),"")
H3:H9	H3	=IFERROR(INDEX(DD_T,MATCH(0,COUNTIF($H$2:H2,DD_T),0)),"")
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
D_L	=SeveralTradesLater!$B$2:$B$11	J3:J7
DD_T	=SeveralTradesLater!$A$2:$A$11	G3, J3:J7, H3:H9

Cells with Data Validation
Cell	Allow	Criteria
E2	List	=OFFSET($H$3,,,MATCH(2,1/($H$3:$H$9<>"")))
E3	List	=OFFSET($J$3,,,MATCH(2,1/($J$3:$J$7<>"")))

 

[SeveralTradesLater]

That's a brilliant answer Kirk. If there's an upgrade to Excel 365 I will definitely use the Unique function and other new functions. Greatly appreciate your help. However, I'm still currently using Excel 2019 and the folks who will be receiving the file also use Excel 2019.

In the interim I have gotten the data validation list to function, by using this array in the source field of my data validation, but the new issue is when I change the value of the cell I'm referencing to a new value. It doesn't provide me with the correct dependent dropdown values for the new value. It continues to provide the dependent dropdowns of the old value.

The "array" I am using is =offset(MA!$B$2,MATCH(FT!,G12,MA!$A$2:$A100,0)-1,0,Countif(MA!$A2:$A100,FT!,G12),1)

MA! = Sheet with two columns DD_T and D_L.
FT = Cell with G12, but using the same array above to provide dependent dropdowns for G13,14,15.etc.

If anyone could please provide advice on how I can resolve this, would be greatly appreciated.

Thanks again Kirk.

 

[KRice]

I'm not sure you've followed by earlier explanation. The solution I described above in columns H:K is for earlier versions of Excel, including Excel 2019. The column G formula was included only to offer insight into how much easier it is to create the necessary lists using Excel 365. For now, please ignore column G.

An example showing your worksheet(s) structure would be helpful (posted with the XL2BB add-in), as I am only guessing about some of the details based on the formulas you've described.
You need a unique list of DD_T items. See my H3 formula, which would be copied into a worksheet in cell H3 and pulled down. I'm assuming the named range DD_T used in this formula is defined in your Name Manager such that the appropriate worksheet name and range is already handled by simply referring to DD_T. Based on the most recent formula you described, I believe this might be (guessing here) MA!$A2:$A100. If you paste this formula into a different location, change the reference to H$2:H2 to the cell immediately above the formula's top location (e.g., if you pasted this formula into cell G2, then you would use G$1:G1 instead of H$2:H2. When you pull this formula down, you should see a list of unique (no duplicates) items in DD_T which eventually transitions into blank cells appearing.

=IFERROR(INDEX(DD_T,MATCH(0,COUNTIF(H$2:H2,DD_T),0)),"")

To form a list of D_L items that are associated with the user-chosen DD_T item (I'm assuming, based on your formula, that this selection in made in cell G12?), use the formula I described in J3. Here I have adapted the formula to something that is probably closer to your workbook. Based on the last formula you described, it sounds like G12 is not on worksheet FORM but on worksheet FT...is that correct? And I am now assuming that the user is not selecting from just one dropdown cell for the initial selection ($E$2 in my example), but instead selecting from the dropdown list on multiple rows (FT!G12, FT!G13, etc.). I'm assuming the named range D_L used in this formula is defined in your Name Manager such that the appropriate worksheet name and range is already handled by simply referring to D_L...and based on the most recent formula you described, I believe this might be MA!$B2:$B100. Here I'm assuming you will paste the formula into cell J3, a couple of columns to the right of the formula mentioned above. Again, if you paste the formula into a different cell, adjust the references to J$3:J3 to correspond with the location of the upper cell holding this formula. And if the data in the DD_T list begins in a cell other than A2, then adjust the reference to ROW($A$1) to correspond to the cell immediately above the DD_T data.

=IF(ROWS(J$3:J3)<=COUNTIF(DD_T,FT!G12),INDEX(D_L,AGGREGATE(15,3,(ROW(DD_T)-ROW($A$1))/(DD_T=FT!G12),ROWS(J$3:J3))),"")

When you pull this formula down, you should see a list of D_L items corresponding to the DD_T selection. Note that this list of corresponding D_L items uses a formula that finds matches only, but it does not exclude duplicate D_L items. For example, if you have two ABC01 / CUP entries, you will get two CUP items in the list created by this formula. I don't know if that is conceivable or desirable...you'll have to determine that. Further refinements would be needed to then exclude the duplicates.

Those are the two main formulas used in helper columns, and the lists created by those formulas are then referenced in two more formulas that are entered into the Data Validation window. That is what my original I3 and K3 formulas do. They show how the OFFSET formula is used to filter out the blanks that appear in the first two lists. You don't need to display these formulas in a worksheet...they are entered into the Data Validation window when you specify that the dropdown list is to be formed from a List derived from a formula Source...and then paste the OFFSET formula in the Source field. The OFFSET formula simply refers to the entire range covered by the two formulas described above, where the anchor for OFFSET is the upper cell location for the list and the MATCH function uses a range that covers the entire list where the list-generating formula is shown...including the blanks produced by the formula. So you will have something similar to:

=OFFSET($H$3,,,MATCH(2,1/($H$3:$H$9<>"")))

for the trimmed list that appears in the dropdown selector for DD_T
...and

=OFFSET($J$3,,,MATCH(2,1/($J$3:$J$7<>"")))

for the trimmed list that appears in the dropdown selector for D_L.

 

[SeveralTradesLater]

Legendary! Thank you Kirk.

 

[KRice]

I'm happy to help.