Index / match? To return an adjacent cell to a min formula

[ollyhughes1982]

[FONT=&quot]Hi. I have this formula in my Excel spreadsheet:[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]=MIN(C4,K4,S4,AA4)[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]It gives me the minimum time value from each race, as the running event is comprised of 4 sub events of which your best time counts. I would like to know, how do I get my spreadsheet to display the adjacent cells to the left and right of the source cell (Time) that contains the min value (i.e. their quickest race of the 4), in the cell adjacent (left and right) to my formula cell. Essentially, populating the blank cells according to the quickest race the person has run.

Can I attach my spreadsheet file to the post?[/FONT]

 

[Fluff]

Having seen post#9
In AJ4
=INDEX($A4:$AG4,MATCH($AI4,$A4:$AA4,0)+COLUMN(A1))
and fill right

 

[ollyhughes1982]

Hi. It seems to work for AH4, but not AJ4. It gives 02:52:48, but it should be giving 12.00% in AJ4, in AJ4, 24.00% in AK4, 2 in AL4, 100 in AM4, 23.00% in AN4 & 12.0 in AO4. i.e. The results from Sub-Event ＃4. as this was their fastest of the 4 races.

e.g. For Joe Bloggs 2 it would be 20.00% in AJ5, 30.00% in AK5, 5 in AL5, 20 in AM5, 33.00% in AN5 & 34.0 in AO5. i.e. The results from Sub-Event ＃1. as this was their fastest of their 4 races.

and so on...

Thanks.

 

[Fluff]

You need to change the cell formats to suit.

 

[ollyhughes1982]

Thanks so much. That seems to work perfectly! I'm sure I will be back with more Excel questions at some point. What a great site!

 

[Fluff]

Glad we could help & thanks for the feedback

 

[ollyhughes1982]

Hi all. Me again. I am now trying to adapt the help you gave me earlier into my main document. Please can you have a look and tell me why not working. Thanks!

https://1drv.ms/x/s!AtIvkRgReIvwgQNdJsfIu7985B7u

 

[Fluff]

For GA5
=INDEX($A$5:$FX5,MATCH($FZ5,$A5:$FXS5,0)+COLUMN(A1))
and
=INDEX($A5:$FX5,MATCH($FZ5,$A5:$FX5,0)-1)
for FY5