modify excel formula

[Geo Jul]

Dear all
May I kindly request to modify this formula as in my work we use excel 2010 and 2016 =TRANSPOSE(UNIQUE(FILTER(D:D,(B:B=F3)*(C:C=G3)))) its work perfect at home with excel 365
I tried with this formula but did not succeed. =IFERROR(INDEX($D$1:$D$1000, SMALL(IF(($B$1:$B$1000=$F18)*($C$1:$C$1000=$G18), MATCH(ROW($D$1:$D$1000), ROW($D$1:$D$1000)), ""), COLUMN(A1))), "")

many thanks

 

[AhoyNC]

Maybe something like this.
This is an array formula and must be entered with CTRL-SHIFT-ENTER in Excel 2010 or 2016.
Drag formula down as needed.

Book1
	A	B	C	D	E	F	G	H
1
2		1	test1	a				List
3		2	test2	b		1	Test1	a
4		3	test3	c				b
5		1	test1	a				g
6		4	test5	v
7		1	test1	b
8		6	test7	f
9		1	test1	g
10		2	test9	h
Sheet1

Cell Formulas
Range	Formula
H3:H5	H3	=IFERROR(INDEX($D$2:$D$10,SMALL(IF(FREQUENCY(IF($B$2:$B$10=$F$3,IF($C$2:$C$10=$G$3,MATCH($D$2:$D$10,$D$2:$D$10,0))),ROW($D$2:$D$10)-ROW($D$2)+1),ROW($D$2:$D$10)-ROW($D$2)+1),ROWS($H$3:H3))),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Geo Jul]

Maybe something like this.
This is an array formula and must be entered with CTRL-SHIFT-ENTER in Excel 2010 or 2016.
Drag formula down as needed.

Book1
	A	B	C	D	E	F	G	H
1
2		1	test1	a				List
3		2	test2	b		1	Test1	a
4		3	test3	c				b
5		1	test1	a				g
6		4	test5	v
7		1	test1	b
8		6	test7	f
9		1	test1	g
10		2	test9	h
Sheet1

Cell Formulas
Range	Formula
H3:H5	H3	=IFERROR(INDEX($D$2:$D$10,SMALL(IF(FREQUENCY(IF($B$2:$B$10=$F$3,IF($C$2:$C$10=$G$3,MATCH($D$2:$D$10,$D$2:$D$10,0))),ROW($D$2:$D$10)-ROW($D$2)+1),ROW($D$2:$D$10)-ROW($D$2)+1),ROWS($H$3:H3))),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

Many Thanks for your responses and your solution Regards

 

[Peter_SSs]

Here is a shorter one that you could also test. I don't think that it should require Ctrl+Shift+Enter

24 01 06.xlsm
	B	C	D	E	F	G	H	I	J	K	L
1
2	1	test1	a
3	2	test2	b		1	test1	a	b	g
4	3	test3	c		2	test2	b
5	1	test1	a		3	test7
6	4	test5	v		4	test5	v	h
7	1	test1	b
8	6	test7	f
9	1	test1	g
10	4	test5	h
Older versions

Cell Formulas
Range	Formula
H3:L6	H3	=IFERROR(INDEX($D:$D,AGGREGATE(15,6,ROW($D$2:$D$10)/(($B$2:$B$10=$F3)*($C$2:$C$10=$G3)*(ISERROR(MATCH($D$2:$D$10,$G3:G3,0)))),1)),"")

 

[Geo Jul]

Here is a shorter one that you could also test. I don't think that it should require Ctrl+Shift+Enter

24 01 06.xlsm
	B	C	D	E	F	G	H	I	J	K	L
1
2	1	test1	a
3	2	test2	b		1	test1	a	b	g
4	3	test3	c		2	test2	b
5	1	test1	a		3	test7
6	4	test5	v		4	test5	v	h
7	1	test1	b
8	6	test7	f
9	1	test1	g
10	4	test5	h
Older versions

Cell Formulas
Range	Formula
H3:L6	H3	=IFERROR(INDEX($D:$D,AGGREGATE(15,6,ROW($D$2:$D$10)/(($B$2:$B$10=$F3)*($C$2:$C$10=$G3)*(ISERROR(MATCH($D$2:$D$10,$G3:G3,0)))),1)),"")

Very geniuses with your formula Many thanks
have a nice day, thanks again

 

[Peter_SSs]

You are welcome. Thanks for the follow-up.