INDEX / MIN Issues

[ollyhughes1982]

[FONT=&quot]Hi.[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]Please see ‘Example’ file via the link, below.[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]https://1drv.ms/x/s!AtIvkRgReIvwgRcNSvXr5zlzQZXA[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]My problem relates to columns BF and BG in certain cases.[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]In cases where the finishing position is the same in column BB and BE (sub-event #1 and sub-event #2: green highlighted area), I want column BH to pick the value from the column which has the higher value in either BA or BD (age-grade), rather than just automatically take the values from sub-event #1, which it seems to be doing.[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]The examples in yellow are coincidentally ok, as the results in sub-event #1 happen to be the one with the higher age-grade score. However, the examples in red need to be selecting the relevant values from BC, BD & BE, rather than AZ, BA & BB - i.e. the ones with the higher age-grade score and then the relevant time for that age-grade score.[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]e.g. In row 19 the BG19 cell should show 66.38% and BF19 should show 00:06:49. Row 20 is similar in that BG20 shows 00:04:02 and 67.88%, but should show 68.91% and 06:34. There are similar cases in rows 38, 46, 78 & 80.[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]In other words, when the finishing positions are the same in both sub-events; I want the data to be pulled from the sub-event which has the higher age-grade score and the relevant time for that score.[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]Thanks in advance.[/FONT]

 

[AhoyNC]

Maybe something like this:

Excel Workbook
	AZ	BA	BB	BC	BD	BE	BF	BG	BH
3	Time	Age-Grade	FinishingPosition	Time	Age-Grade	FinishingPosition	Time	Age-Grade	FinishingPosition
4	0:07:09	61.52%	3	0:06:33	67.15%	2	0:06:33	67.15%	2
5	0:05:34	71.92%	1	0:06:07	65.45%	1	0:06:07	65.45%	1
Sheet

 

[ollyhughes1982]

That didn't work unfortunately. Thanks for replying though

 

[AlanY]

try this

Book1
	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI
3	Time	Age-Grade	Finishing Position	Time	Age-Grade	Finishing Position	Time	Age-Grade	Finishing Position	Points League Event Points
4	00:07:09	61.52%	3	00:06:33	67.15%	2	00:06:33	67.15%	2	149
5	00:05:34	71.92%	1	00:06:07	65.45%	1	00:05:34	71.92%	1	150
6							#N/A	0.00%	0	0
7							#N/A	0.00%	0	0
Results & Members Data

Cell Formulas
Range	Formula
BF4	=INDEX(AZ4:BE4,MATCH(BG4,AZ4:BE4,0)-1)
BG4	=IF(BB4=BE4,MAX(BA4,BD4),IFERROR(INDEX(AZ4:BE4,MATCH(BH4,AZ4:BE4,0)-1),0))

 

[ollyhughes1982]

That works a treat! Thanks so much

 

[AlanY]

you're welcome