Index/Match error in formula

[BradleyS]

I have the following formula's which sort of work fine (shown below) to transpose the data in the first table below, except if the ID value (in column A) is the same value as the result (in column C)

So if C2 was changed to 8, it would ignore it and then mess up all the other figures for that ID row

I can't seem to work out how to resolve this, but still maintain the final result. Help would be very appreciated

This is my data table:

Excel 2010
	A	B	C
1	ID	date	result
2	8	28/11/2018	5
3	8	28/08/2018	4
4	8	28/05/2018	7
5	25	16/12/2013	6.15
6	25	13/06/2013	6
7	25	01/02/2013	5.65
8	40	15/08/2012	0.56
9	69	09/06/2016	2
10	69	03/04/2013	3.34
11	76	28/03/2011	0.81
12	187	04/12/2013	3.39
13	187	22/07/2011	2.86
14	211	19/08/2013	2.92
15	211	19/04/2013	2.9
16	219	28/07/2011	5.88
PSA

This is the output including the formulas:

Excel 2010
	E	F	G	H	I	J	K	L	M	N	O
1	ID	date	result 1	date	result 2	date	result 3	date	result 4	date	result 5
2	8	28/11/2018	5	28/08/2018	4	28/05/2018	7
3	25	16/12/2013	6.15	13/06/2013	6	01/02/2013	5.65
4	40	15/08/2012	0.56
5	69	09/06/2016	2	03/04/2013	3.34
6	76	28/03/2011	0.81
7	187	04/12/2013	3.39	22/07/2011	2.86
8	211	19/08/2013	2.92	19/04/2013	2.9
9	219	28/07/2011	5.88
PSA

Cell Formulas
Range	Formula
E2	{=INDEX($A$2:$A$16, MATCH(0, COUNTIF($E$1:$E1, $A$2:$A$16), 0))}
F2	{=IFERROR(INDEX($B$2:$B$16, MATCH(0, COUNTIF($E2:E2,$B$2:$B$16)+IF($A$2:$A$16<>$E2, 1, 0), 0)), "")}
G2	{=IFERROR(INDEX($C$2:$C$16, MATCH(0, COUNTIF($E2:F2,$C$2:$C$16)+IF($A$2:$A$16<>$E2, 1, 0), 0)), "")}
H2	{=IFERROR(INDEX($B$2:$B$16, MATCH(0, COUNTIF($E2:G2,$B$2:$B$16)+IF($A$2:$A$16<>$E2, 1, 0), 0)), "")}
I2	{=IFERROR(INDEX($C$2:$C$16, MATCH(0, COUNTIF($E2:H2,$C$2:$C$16)+IF($A$2:$A$16<>$E2, 1, 0), 0)), "")}
J2	{=IFERROR(INDEX($B$2:$B$16, MATCH(0, COUNTIF($E2:I2,$B$2:$B$16)+IF($A$2:$A$16<>$E2, 1, 0), 0)), "")}
K2	{=IFERROR(INDEX($C$2:$C$16, MATCH(0, COUNTIF($E2:J2,$C$2:$C$16)+IF($A$2:$A$16<>$E2, 1, 0), 0)), "")}
L2	{=IFERROR(INDEX($B$2:$B$16, MATCH(0, COUNTIF($E2:K2,$B$2:$B$16)+IF($A$2:$A$16<>$E2, 1, 0), 0)), "")}
M2	{=IFERROR(INDEX($C$2:$C$16, MATCH(0, COUNTIF($E2:L2,$C$2:$C$16)+IF($A$2:$A$16<>$E2, 1, 0), 0)), "")}
N2	{=IFERROR(INDEX($B$2:$B$16, MATCH(0, COUNTIF($E2:M2,$B$2:$B$16)+IF($A$2:$A$16<>$E2, 1, 0), 0)), "")}
O2	{=IFERROR(INDEX($C$2:$C$16, MATCH(0, COUNTIF($E2:N2,$C$2:$C$16)+IF($A$2:$A$16<>$E2, 1, 0), 0)), "")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[RasGhul]

Hi Bradley,

I had to use a helper row F2-O2 as I don't think you can split array formulas for different columns of data.

Note that you will need to repeat the process across columns for how many results you will encounter.

Book1
	A	B	C	E	F	G	H	I	J	K	L	M	N	O
1	ID	date	result	ID	date	result 1	date	result 2	date	result 3	date	result 4	date	result 5
2	8	28-11-18	5		1	1	2	2	3	3	4	4	5	5
3	8	28-08-18	4	8	28-11-18	5	28-08-18	4	28-05-18	7
4	8	28-05-18	7	25	16-12-13	6.15	13-06-13	6	01-02-13	5.65
5	25	16-12-13	6.15	40	15-08-12	0.56
6	25	13-06-13	6	69	09-06-16	2	03-04-13	3.34
7	25	01-02-13	5.65	76	28-03-11	0.81
8	40	15-08-12	0.56	187	04-12-13	3.39	22-07-11	2.86
9	69	09-06-16	2	211	19-08-13	2.92	19-04-13	2.9
10	69	03-04-13	3.34	219	28-07-11	5.88
11	76	28-03-11	0.81
12	187	04-12-13	3.39
13	187	22-07-11	2.86
14	211	19-08-13	2.92
15	211	19-04-13	2.9
16	219	28-07-11	5.88
Sheet1

Cell Formulas
Range	Formula
F2	=COUNTIF($F$1:F1,"Date")
F3	{=IF(F$2>COUNTIF($A$2:$A$16,$E3),"",IF(ISNUMBER($E3),INDEX($B$2:$B$16,SMALL(IF($A$2:$A$16=$E3,ROW($A$2:$A$16)-ROW($A$2)+1),F$2)),""))}
E3	{=IFNA(INDEX($A$2:$A$16, MATCH(0, COUNTIF($E$1:$E1, $A$2:$A$16), 0)),"")}
G3	{=IF(G$2>COUNTIF($A$2:$A$16,$E3),"",IF(ISNUMBER($E3),INDEX($C$2:$C$16,SMALL(IF($A$2:$A$16=$E3,ROW($A$2:$A$16)-ROW($A$2)+1),G$2)),""))}
H3	{=IF(H$2>COUNTIF($A$2:$A$16,$E3),"",IF(ISNUMBER($E3),INDEX($B$2:$B$16,SMALL(IF($A$2:$A$16=$E3,ROW($A$2:$A$16)-ROW($A$2)+1),H$2)),""))}
I3	{=IF(I$2>COUNTIF($A$2:$A$16,$E3),"",IF(ISNUMBER($E3),INDEX($C$2:$C$16,SMALL(IF($A$2:$A$16=$E3,ROW($A$2:$A$16)-ROW($A$2)+1),I$2)),""))}
J3	{=IF(J$2>COUNTIF($A$2:$A$16,$E3),"",IF(ISNUMBER($E3),INDEX($B$2:$B$16,SMALL(IF($A$2:$A$16=$E3,ROW($A$2:$A$16)-ROW($A$2)+1),J$2)),""))}
K3	{=IF(K$2>COUNTIF($A$2:$A$16,$E3),"",IF(ISNUMBER($E3),INDEX($C$2:$C$16,SMALL(IF($A$2:$A$16=$E3,ROW($A$2:$A$16)-ROW($A$2)+1),K$2)),""))}
L3	{=IF(L$2>COUNTIF($A$2:$A$16,$E3),"",IF(ISNUMBER($E3),INDEX($B$2:$B$16,SMALL(IF($A$2:$A$16=$E3,ROW($A$2:$A$16)-ROW($A$2)+1),L$2)),""))}
M3	{=IF(M$2>COUNTIF($A$2:$A$16,$E3),"",IF(ISNUMBER($E3),INDEX($B$2:$B$16,SMALL(IF($A$2:$A$16=$E3,ROW($A$2:$A$16)-ROW($A$2)+1),M$2)),""))}
N3	{=IF(N$2>COUNTIF($A$2:$A$16,$E3),"",IF(ISNUMBER($E3),INDEX($B$2:$B$16,SMALL(IF($A$2:$A$16=$E3,ROW($A$2:$A$16)-ROW($A$2)+1),N$2)),""))}
O3	{=IF(O$2>COUNTIF($A$2:$A$16,$E3),"",IF(ISNUMBER($E3),INDEX($B$2:$B$16,SMALL(IF($A$2:$A$16=$E3,ROW($A$2:$A$16)-ROW($A$2)+1),O$2)),""))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[BradleyS]

Thank you. That works perfect now.