INDEX/AGGREGATE/ROW Formula

[James8761]

I had this formula in a spreadsheet where I was kindly helped from here. I run a running club and I am trying to improve my spreadsheet.

The formula I had was =IFERROR(INDEX(Runners!$P$2:$P$3843,AGGREGATE(15,6,(ROW(Runners!$P$2:$P$3843)-ROW(!$B$2)+1)/(Runners!$A$2:$A$3843='Race 1'!$B339), COLUMNS($O339:O339))),"")

Please ignore the tab names, it is the formula I am struggling with. The formula above gives me the answer 56. Which is correct, no problem with that, but I am trying to add in text of " - " and also another column (their finishing position). So the full answer would be 56 - 1 etc.

I have copied some dummy data below: The data covers the range A1:P30, with the headers being on Row 1. This tab is called Data.

Name

Number

Position

Total

Date

Conditions

Quick

Course

Distance

Attendance

Track

Classifications

Race Type

Age Range

Paid

Rating

Turner

55​

5​

25​

05/12/2019​

Cold

Yes

Birmingham

10k

Zero

Quick

Mixed

Mixed

Any

Y

81​

Turner

64​

4​

25​

18/11/2019​

Windy

Yes

Manchester

8k

Zero

Slow

Mixed

Mixed

Any

Y

82​

Davis

33​

7​

25​

18/11/2019​

Windy

Yes

Manchester

8k

Zero

Slow

Mixed

Mixed

Any

Y

65​

Turner

62​

3​

25​

07/11/2019​

Rain

Yes

Edinburgh

10k

Zero

Slow

Men

Men

Any

Y

85​

Davis

59​

2​

25​

07/11/2019​

Rain

Yes

Edinburgh

10k

Zero

Slow

Men

Men

Any

Y

88​

Turner

61​

7​

25​

16/10/2019​

Rain

No

Birmingham

5k

Zero

Slow

Men

Men

Any

Y

87​

Turner

67​

1​

25​

19/09/2019​

Sunny

Yes

Exeter

10k

Zero

Medium

Men

Men

Any

Y

74​

Davis

69​

2​

25​

19/09/2019​

Sunny

Yes

Exeter

10k

Zero

Medium

Men

Men

Any

Y

71​

Turner

62​

4​

25​

10/09/2019​

Sunny

Yes

Birmingham

8k

Zero

Good

Mixed

Mixed

Any

Y

52​

Turner

71​

1​

30​

26/08/2019​

Rain

Yes

Manchester

10k

Zero

Slow

Mixed

Mixed

Any

Y

75​

Davis

98​

2​

30​

26/08/2019​

Rain

Yes

Manchester

10k

Zero

Slow

Mixed

Mixed

Any

Y

65​

Turner

53​

6​

40​

06/08/2019​

Windy

Yes

London

10k

Zero

Slow

Mixed

Mixed

Any

Y

53​

Turner

58​

6​

40​

24/07/2019​

Hot

Yes

Cardiff

10k

Zero

Good

Mixed

Mixed

Any

Y

55​

Turner

57​

9​

40​

17/06/2019​

Rain

Yes

Cardiff

5k

Zero

Slow

Men

Men

Any

Y

58​

Turner

43​

0​

40​

10/06/2019​

Hot

Yes

Manchester

10k

Zero

Quick

Men

Men

Any

Y

58​

Davis

48​

5​

40​

10/06/2019​

Hot

Yes

Manchester

10k

Zero

Quick

Men

Men

Any

Y

68​

Turner

64​

4​

40​

28/05/2019​

Rain

Yes

London

10k

Zero

Slow

Mixed

Mixed

Any

Y

76​

Davis

75​

2​

45​

28/05/2019​

Rain

Yes

London

10k

Zero

Slow

Mixed

Mixed

Any

Y

92​

Turner

59​

6​

40​

11/05/2019​

Windy

Yes

Birmingham

8k

Zero

Slow

Mixed

Mixed

Any

Y

60​

Turner

55​

4​

40​

26/11/2018​

Cold

Yes

Manchester

10k

Zero

Slow

Mixed

Mixed

Any

Y

70​

Turner

36​

4​

40​

08/11/2018​

Cold

Yes

Edinburgh

6f

Zero

Slow

Mixed

Mixed

Any

Y

70​

Davis

25​

3​

40​

08/11/2018​

Cold

Yes

Edinburgh

6f

Zero

Slow

Mixed

Mixed

Any

Y

85​

Turner

59​

2​

40​

04/11/2018​

Cold

No

Edinburgh

5f

Zero

Slow

Mixed

Mixed

Any

Y

70​

Turner

40​

6​

40​

24/08/2018​

Sunny

No

Birmingham

10k

Zero

Quick

Men

Men

Any

Y

71​

Davis

55​

2​

11​

24/08/2018​

Sunny

No

Birmingham

10k

Zero

Quick

Men

Men

Any

Y

84​

Turner

61​

2​

50​

11/08/2018​

Hot

Yes

Cardiff

8k

Zero

Quick

Men

Men

U-25

Y

65​

Davis

12​

9​

46​

11/08/2018​

Hot

Yes

Cardiff

8k

Zero

Quick

Men

Men

U-25

Y

33​

Turner

44​

7​

50​

30/07/2018​

Hot

No

Birmingham

10k

Zero

Quick

Men

Men

U-25

Y

62​

Turner

50​

4​

50​

26/05/2018​

Rain

No

Manchester

5k

Zero

Slow

Men

Men

U-25

Y

64​

On the tab next to it, named Summary. I have put headings such as Name, Conditions, Course, Distance and Track. Next to those headings I would type the persons naem, the conditions, today's course, the distance and the track.

This would then bring back the most recent results (most recent date first) and then their finishing position. The columns I am trying to capture are 'Rating' and 'Position'. In the example below. I have typed in Turner, Rain, Birmingham, 10k and Slow.

It then looks at the last 5 occurrences of these conditions and brings back the Rating and the Position, results below 85 - 3 87 - 7 75 -1 58 - 9 76 - 4 for the last time "Turner" ran in Conditions that were "Rain" plus a - (with a space either side of the -) then their finishing position. In the screen dump below the merge formatting hasn't worked. There should be 5 cells each for Condition, Course, Distance and Track. I have tried using the xl2bb add in, I hope this has worked.

Any help greatly appreciated.

Name	Turner	Conditions	Rain	Course	Birmingham	Distance	10k	Track	Slow
Condition	Course	Distance	Track
85 - 3	87 - 7	75 - 1	58 - 9	76 - 4	81 - 5	87 - 7	52 - 4	60 - 6	71 - 6	81 - 5	85 - 3	74 - 1	75 - 1	53 - 6	82 - 4	85 - 3	87 - 7	75 - 1	53 - 6

Runner Data.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Name	Number	Position	Total	Date	Conditions	Quick	Course	Distance	Attendance	Track	Classifications	Race Type	Age Range	Paid	Rating
2	Turner	55	5	25	05/12/2019	Cold	Yes	Birmingham	10k	Zero	Quick	Mixed	Mixed	Any	Y	81
3	Turner	64	4	25	18/11/2019	Windy	Yes	Manchester	8k	Zero	Slow	Mixed	Mixed	Any	Y	82
4	Davis	33	7	25	18/11/2019	Windy	Yes	Manchester	8k	Zero	Slow	Mixed	Mixed	Any	Y	65
5	Turner	62	3	25	07/11/2019	Rain	Yes	Edinburgh	10k	Zero	Slow	Men	Men	Any	Y	85
6	Davis	59	2	25	07/11/2019	Rain	Yes	Edinburgh	10k	Zero	Slow	Men	Men	Any	Y	88
7	Turner	61	7	25	16/10/2019	Rain	No	Birmingham	5k	Zero	Slow	Men	Men	Any	Y	87
8	Turner	67	1	25	19/09/2019	Sunny	Yes	Exeter	10k	Zero	Medium	Men	Men	Any	Y	74
9	Davis	69	2	25	19/09/2019	Sunny	Yes	Exeter	10k	Zero	Medium	Men	Men	Any	Y	71
10	Turner	62	4	25	10/09/2019	Sunny	Yes	Birmingham	8k	Zero	Good	Mixed	Mixed	Any	Y	52
11	Turner	71	1	30	26/08/2019	Rain	Yes	Manchester	10k	Zero	Slow	Mixed	Mixed	Any	Y	75
12	Davis	98	2	30	26/08/2019	Rain	Yes	Manchester	10k	Zero	Slow	Mixed	Mixed	Any	Y	65
13	Turner	53	6	40	06/08/2019	Windy	Yes	London	10k	Zero	Slow	Mixed	Mixed	Any	Y	53
14	Turner	58	6	40	24/07/2019	Hot	Yes	Cardiff	10k	Zero	Good	Mixed	Mixed	Any	Y	55
15	Turner	57	9	40	17/06/2019	Rain	Yes	Cardiff	5k	Zero	Slow	Men	Men	Any	Y	58
16	Turner	43	0	40	10/06/2019	Hot	Yes	Manchester	10k	Zero	Quick	Men	Men	Any	Y	58
17	Davis	48	5	40	10/06/2019	Hot	Yes	Manchester	10k	Zero	Quick	Men	Men	Any	Y	68
18	Turner	64	4	40	28/05/2019	Rain	Yes	London	10k	Zero	Slow	Mixed	Mixed	Any	Y	76
19	Davis	75	2	45	28/05/2019	Rain	Yes	London	10k	Zero	Slow	Mixed	Mixed	Any	Y	92
20	Turner	59	6	40	11/05/2019	Windy	Yes	Birmingham	8k	Zero	Slow	Mixed	Mixed	Any	Y	60
21	Turner	55	4	40	26/11/2018	Cold	Yes	Manchester	10k	Zero	Slow	Mixed	Mixed	Any	Y	70
22	Turner	36	4	40	08/11/2018	Cold	Yes	Edinburgh	6f	Zero	Slow	Mixed	Mixed	Any	Y	70
23	Davis	25	3	40	08/11/2018	Cold	Yes	Edinburgh	6f	Zero	Slow	Mixed	Mixed	Any	Y	85
24	Turner	59	2	40	04/11/2018	Cold	No	Edinburgh	5f	Zero	Slow	Mixed	Mixed	Any	Y	70
25	Turner	40	6	40	24/08/2018	Sunny	No	Birmingham	10k	Zero	Quick	Men	Men	Any	Y	71
26	Davis	55	2	11	24/08/2018	Sunny	No	Birmingham	10k	Zero	Quick	Men	Men	Any	Y	84
27	Turner	61	2	50	11/08/2018	Hot	Yes	Cardiff	8k	Zero	Quick	Men	Men	U-25	Y	65
28	Davis	12	9	46	11/08/2018	Hot	Yes	Cardiff	8k	Zero	Quick	Men	Men	U-25	Y	33
29	Turner	44	7	50	30/07/2018	Hot	No	Birmingham	10k	Zero	Quick	Men	Men	U-25	Y	62
30	Turner	50	4	50	26/05/2018	Rain	No	Manchester	5k	Zero	Slow	Men	Men	U-25	Y	64
Data

Cell Formulas
Range	Formula
B6	B6	=B24

Runner Data.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1
2
3		Name	Turner	Conditions	Rain	Course	Birmingham	Distance	10k	Track	Slow
4
5
6
7		Condition					Course					Distance					Track
8		85 - 3	87 - 7	75 - 1	58 - 9	76 - 4	81 - 5	87 - 7	52 - 4	60 - 6	71 - 6	81 - 5	85 - 3	74 - 1	75 - 1	53 - 6	82 - 4	85 - 3	87 - 7	75 - 1	53 - 6
Summary

Cell Formulas
Range	Formula
B8:U8	B8	=B13&" - "&B14

 

[Fluff]

How about

=INDEX(Data!$P$2:$P$30,AGGREGATE(15,6,(ROW(Data!$P$2:$P$30)-ROW(Data!$P$2)+1)/(Data!$A$2:$A$30=$C$3)/(Data!$F$2:$F$30=$E$3),COLUMNS($B8:B8)))&" - "&INDEX(Data!$C$2:$C$30,AGGREGATE(15,6,(ROW(Data!$P$2:$P$30)-ROW(Data!$P$2)+1)/(Data!$A$2:$A$30=$C$3)/(Data!$F$2:$F$30=$E$3),COLUMNS($B8:B8)))

 

[James8761]

How about

=INDEX(Data!$P$2:$P$30,AGGREGATE(15,6,(ROW(Data!$P$2:$P$30)-ROW(Data!$P$2)+1)/(Data!$A$2:$A$30=$C$3)/(Data!$F$2:$F$30=$E$3),COLUMNS($B8:B8)))&" - "&INDEX(Data!$C$2:$C$30,AGGREGATE(15,6,(ROW(Data!$P$2:$P$30)-ROW(Data!$P$2)+1)/(Data!$A$2:$A$30=$C$3)/(Data!$F$2:$F$30=$E$3),COLUMNS($B8:B8)))

Wow, that's absolutely fantastic. Just been sitting here trying it all out. That is exactly what I needed. Thank you so much

 

[Fluff]

You're welcome & thanks for the feedback.