How can I retrieve information about a value from another sheet depending on which value is returned from a MAX function? (Let me Explain!)

[leighhobson89]

Hi guys. Screenshot 1 below shows a 'stats' table. The issue I am asking about relates to the "Date (BOR)" column. So the idea is that the "Best (Over Route)" column returns the result of the following formula:

=MIN('Route BenchMarks'!$E$36, MIN('Route 1'!$A$36:'Route 1'!$ZZ$36))

Screenshot 2 shows an example of what could get returned in C36; the whole row will be blank except for these values every third column; each new time will be pasted in three columns to the right of the last one as I do the events, so the formula finds the lowest time of each of these and prints it in the "Best (Over Route) column, or if the benchmark time is better it prints that. This works fine.

What I want for the "Date (BOR)" column, is to retrieve the relevant hardcoded value in the date field, so two cells directly above the relevant time that is retrieved in the formula for the "Best (Over Route)" column. For example, if it retrieved our example in Screenshot 2 as the best time, then the Date (BOR) column would print "16/02/2021". If the benchmark time was better, which is a hardcoded time on a seperate sheet, I would like it to just print "Benchmark".

I tried playing around with OFFSET function, but with little success. TIA.

Screenshot 1

Screenshot 2

 

[Fluff]

Is this for Excel or Google Sheets?

 

[leighhobson89]

Is this for Excel or Google Sheets?

I am doing it on Google Sheets but I was hoping that the concept could be explained as if it was Excel and then I could sort out the syntax from there if this is only an Excel forum. (That's assuming the solution would be different).

 

[Fluff]

In future questions about Sheets should be posted in the General Discussion & Other Applications section (I have moved it this time) and you should clearly state that it's for sheets. Not all Excel Formulae work in sheets.

That said you can try

=IF(J2='Route BenchMarks'!$E$36,"Benchmark",INDEX('Route 1'!$A$34:'Route 1'!$ZZ$34,MATCH(MIN('Route 1'!$A$36:'Route 1'!$ZZ$36),'Route 1'!$A$36:'Route 1'!$ZZ$36,0)))

 

[leighhobson89]

That worked a treat, sorry for the breach of etiquette, I don't think Sheets existed the last time I used this website. Thanks ever so much, I will mark it solved.

 

[Fluff]

You're welcome & thanks for the feedback.