Problems converting array formula to dependant drop down list

[gaosiyou]

Hi All,

I've been trying to find an array formula for a dependant drop down list I saw a few years ago.

Basically, I have three Source columns (Category, SubCategory, Details) that I need to work with.

Constraints:
Must not use VBA, Helper Columns, Macros
Source is not defined as a Table
Source is not sorted
Details Category has Blanks
around 450 Rows in source (pretty stable) but data is not static

I need to be able to pull a list of unique values for the drop down, based on the value in a given cell.

Example source data (Trimmed)

Category SubCategory Details
Espnts Acceil Acceil
Espnts Zonant
Espnts Murons
Espnts Ateées Atede)
Espnts Ateées Atete)
Espnts Ateées Atete)
Tranel Autnel
Tranel Poste) DSCDSC
Tranel Poste) RSSSSC
Tranel Comion
Coneil Bureau Bur0''
Coneil Bureau Burx12
Coneil Espeil ± 99x9
Coneil Espeil ± 9 PF
Coneil Espeil ± 9res
Coneil Poste) Poseil
Coneil Poste) PosCST
Coneil Bureau Adj0''
Coneil Bureau Adj0''
Coneil Bureau Salrs.
Coneil Esptif Zonrs.
Coneil Bureau Salrs.
Coneil Bureau
Coneil Bureau Salrs.
Opénel Mécée)

Using the following array formula on multiple rows in sheet 2, and ... given sheet2!$F$2="Bureau",
I obtain the following Unique Values in Column G...

{=IFERROR(INDEX(Details, SMALL(IF(($F$2=SubCategory)*(COUNTIF($G$1:G1, Details)=0), ROW(SubCategory)-MIN(ROW(SubCategory))+1, ""), 1)),"")}

Column G
1 Bur0''
2 Burx12
3 Adj0''
4 Salrs.
5

What I would like is a drop down in $G$2 where the values available are {Bur0'', Burx12, Adj0'', Salrs} if F2 = "Bureau"

Any and All help would be appreciated

Thanks,

 

[Rhodie72]

Can't help you without VBA

 

[Mike Szczesny]

Can you something like this. I used a very simple example, but this illustrates how the formula works. I used data validation to select d. Hope this helps.
Mike Szczesny

Excel 2012
	A	B	C	D	E	F	G
1	Type	Subcategory	Details	d			a
2	1	a	a		w		b
3	1	b			r		c
4	1	c			k		d
5	1	d	w		s		e
6	2	d	r		x		f
7	2	d	r				g
8	2	e
9	3	f	g
10	3	f	h
11	3	i	j
12	3	d	k
13	3	e	m
14	3	f	n
15	4	g	p
16	4	g	q
17	4	d	r
18	4	d	s
19	4	d	x
Sheet1

Cell Formulas
Range	Formula
E2	{=IFERROR(INDEX($C$2:$C$19,SMALL(IF(FREQUENCY(IF($C$2:$C$19<>"",IF($B$2:$B$19=$D$1,MATCH($C$2:$C$19,$C$2:$C$19,0))),ROW($C$2:$C$19)-ROW($C$2)+1),ROW($C$2:$C$19)-ROW($C$2)+1),ROWS($E$2:E2)))," ")}
Press CTRL+SHIFT+ENTER to enter array formulas.