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]

 

[ollyhughes1982]

Here's a link to the file. Thanks.

[FONT=&quot]https://www.icloud.com/iclouddrive/078Jjs_xLH6WtNgqzvWDSehIg#Excel_Help_Needed[/FONT]

 

[Special-K99]

You'd have to do a index(...match() ) and subtract/add 1 from/to the column to retrieve data either side.
e.g.
=INDEX(A4:ZZ4,1,MATCH(MIN(C4:K4,S4,AA4),C4:AA4,0)-1)
=INDEX(A4:ZZ4,1,MATCH(MIN(C4:K4,S4,AA4),C4:AA4,0)+1)

Beware of repeated data, e.g. C4=K4, S4=AA4 though this is probably unlikely it is possible.


You cant attach files on this forum. There are tools on this forum for adding small spreadsheet images

https://www.mrexcel.com/forum/about-board/508133-attachments.html

Or upload the file to an online storage site and post a link to it, though some people may not download the file for fear of viruses.

 

[ollyhughes1982]

Thanks so much. Could you perhaps check it works in the file? This is such a great help, thanks again.

 

[Special-K99]

Here's a link to the file. Thanks.

[FONT="]https://www.icloud.com/iclouddrive/078Jjs_xLH6WtNgqzvWDSehIg#Excel_Help_Needed[/FONT]

I dont really want to download iCloud for Windows as this is my works PC, I just want the file.
This is liable to put certain users off, ie those who want to help you.

 

[James006]

Hello,

For the cell to the left ..you can test : =INDIRECT(ADDRESS(4,MATCH(MIN(C4,K4,S4,AA4),$4:$4,0)-1,4))

Hope this will help

 

[ollyhughes1982]

OK. Is there another way to share it? Via my OneDrive account, perhaps?

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

 

[ollyhughes1982]

Hi. Thanks for that. I tried this and it just gave me 00:00:00 in cell AJ4?

 

[ollyhughes1982]

Apologies, that =seems to work for the left side (AH4), I accidentally tested it in AJ4. What would I need for AJ4, AK4, AL4, AM4, AN4 & AO4? Thanks

 

[Fluff]

How about, in AH4
=INDEX(A4:ZZ4,MATCH(AI4,A4:AA4,0)-1)
and in AJ4
=INDEX(A4:ZZ4,MATCH(AI4,A4:AA4,0)+1)