Need help with array for multiple values

[JumboCactuar]

Hi,
i have this formula:

{=(INDEX($A$2:$F$30000,MATCH(1,($A$2:$A$30000=D2)*($B$2:$B$30000=B2),0),3))}

which outputs the value in the third column of range when the criteria matches.

However if the criteria has multiple matches i dont know how to output all these, ideally output in adjacent cells horizontally
Ive tried dragging the array across but this doesnt work

Appreciate any help

 

[wideboydixon]

Book1
	A	B	C	D	E	F	G	H	I
2	1	a	A	1	A	G	M	S	Y
3	2	b	B	2	B	H	N	T	Z
4	3	a	C	3	C	I	O	U
5	1	b	D	1	D	J	P	V
6	2	a	E	2	E	K	Q	W
7	3	b	F	3	F	L	R	X
8	1	a	G	1	A	G	M	S	Y
9	2	b	H	2	B	H	N	T	Z
10	3	a	I	3	C	I	O	U
11	1	b	J	1	D	J	P	V
12	2	a	K	2	E	K	Q	W
13	3	b	L	3	F	L	R	X
14	1	a	M	1	A	G	M	S	Y
15	2	b	N	2	B	H	N	T	Z
16	3	a	O	3	C	I	O	U
17	1	b	P	1	D	J	P	V
18	2	a	Q	2	E	K	Q	W
19	3	b	R	3	F	L	R	X
20	1	a	S	1	A	G	M	S	Y
21	2	b	T	2	B	H	N	T	Z
22	3	a	U	3	C	I	O	U
23	1	b	V	1	D	J	P	V
24	2	a	W	2	E	K	Q	W
25	3	b	X	3	F	L	R	X
26	1	a	Y	1	A	G	M	S	Y
27	2	b	Z	2	B	H	N	T	Z
Sheet1

Cell Formulas
Range	Formula
E2	{=IFERROR(INDEX($C$2:$C$30000,SMALL(IF($A$2:$A$30000=$D2,IF($B$2:$B$30000=$B2,ROW($A$2:$A$30000)-ROW($A$2)+1)),COLUMNS($E$2:E$2))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

Copy formula in E2 across and down as necessary. Is that the kind of thing you're after?

WBD

 

[JumboCactuar]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]E2[/TH]
[TD="align: left"]{=IFERROR(INDEX($C$2:$C$30000,SMALL(IF($A$2:$A$30000=$D2,IF($B$2:$B$30000=$B2,ROW($A$2:$A$30000)-ROW($A$2)+1)),COLUMNS($E$2:E$2))),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



Copy formula in E2 across and down as necessary. Is that the kind of thing you're after?

WBD

perfect it works thanks

only 1 thing, i have this also to get the row number

{=ROW(INDEX($A$2:$F$30000,MATCH(1,($A$2:$A$30000=D2)*($B$2:$B$30000=B2),0),3))}

but cant seem to add row into your formula

 

[wideboydixon]

Try like this?

{=IFERROR(ROW(INDEX($C$2:$C$30000,SMALL(IF($A$2:$A$30000=$D2,IF($B$2:$B$30000=$B2,ROW($A$2:$A$30000)-ROW($A$2)+1)),COLUMNS($E$2:E$2)))),"")}

WBD

 

[JumboCactuar]

Try like this?

{=IFERROR(ROW(INDEX($C$2:$C$30000,SMALL(IF($A$2:$A$30000=$D2,IF($B$2:$B$30000=$B2,ROW($A$2:$A$30000)-ROW($A$2)+1)),COLUMNS($E$2:E$2)))),"")}

WBD

ye working thank you