# Help with Index/Match?

#### slam

##### Well-known Member
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

### Excel Facts

Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
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))

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.

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!

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

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

Thank you, that was it!

Replies
1
Views
121
Replies
2
Views
116
Replies
5
Views
207
Replies
3
Views
1K
Replies
7
Views
171

1,196,268
Messages
6,014,344
Members
441,816
Latest member
Klingon1960

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back