Help with Index/Match?

[slam]

I have a records worksheet from a racing league.

There are 20 races with a corresponding worksheet for each.

On the records worksheet, I have the worksheet names in R1 through AK1.

Times are contained on each of the 20 worksheets in the cells E2:E17. I have obtained the fastest time with the formula:

=MIN(INDIRECT("'"&R1&"'!$E$2:$E$17"))

However, now I need to get the name of the participant from B2:B17 on the worksheet name that matches row 1 (R1) in this case.

Note that I am looking for the fastest time/participant per race, not over all 20 races.

Could someone provide some assistance with this?

Thanks

 

[AlphaFrog]

Try something like this where R2 is the min value from your other formula.

=INDEX(INDIRECT("'"&R1&"'!B2:B17"),MATCH(R2,INDIRECT("'"&R1&"'!E2:E17"),0))

 

[Snakehips]

slam,

If say your time formula is in R3 then in say R4 use......

=INDEX(INDIRECT("'"&R1&"'!$F$2:$F$17"),MATCH(R3, INDIRECT("'"&R1&"'!$E$2:$E$17"),0))

Hope that helps.

 

[slam]

Try something like this where R2 is the min value from your other formula.

=INDEX(INDIRECT("'"&R1&"'!B2:B17"),MATCH(R2,INDIRECT("'"&R1&"'!E2:E17"),0))

Thank you! That works perfectly!

Perhaps you could help me with something else related to this. I'm just trying to add an ISBLANK or a ="" but I'm having no luck with either. I've tried it with my MIN formula so far, but ISBLANK is showing a 0:00.000 result, and ="" is showing a #REF! error.

While there's no formula in the cell I'm checking, there is a list/data validation, so that's why I thought I might need to try =""

Here are what I've tried for each of them. The cell I'm checking is B2 on each of the race worksheets (yes, I've moved some stuff around so cell references are slightly different now)

=IF(ISBLANK(INDIRECT(""&A2&"'!B2")),"",MIN(INDIRECT("'"&A2&"'!$E$2:$E$17")))

=IF(INDIRECT(""&A2&"'!B2")="","",MIN(INDIRECT("'"&A2&"'!$E$2:$E$17")))

Where am I going wrong? I'll be applying this to the formula you just provided as well, once I figure it out.

Thanks!

 

[AlphaFrog]

This should work. I think you just had a typo.

=IF(ISBLANK(INDIRECT("'"&A2&"'!B2")),"",MIN(INDIRECT("'"&A2&"'!$E$2:$E$17")))

 

[slam]

Thank you, that was it!

 

[slam]

You may be interested in this thread I made for the 2nd part of my problem: http://www.mrexcel.com/forum/excel-questions/694272-not-sure-how-handle-if-=-formula.html