traveler911 Posts: 6, Reputation: 1 New Member #1 Nov 4, 2013, 06:35 PM
Vlookup for best time
I am attempting to display on a weeksheet, the fastest time ran on a given race. I have tried vlookup, and min function, and IF's... obviously I'm stuck.

On the enclosed file you will find that two columns are highlighted. The first is the length of the given race, and is what the cell will be looking for on the main worksheet.
The second column is the time.

On the main page is lists
3.1
6.2
13.1
26.2
What I would like it to do it list the fastest time ran for each distance next to it.
The list is currently short, but will grow very long.

Thanks for taking time to ponder this.
Kent
Attached Files
1. Book1.xls (36.5 KB, 83 views)
2.  Kevin-Radstock Posts: 8, Reputation: 3 New Member #2 Nov 4, 2013, 10:58 PM
May be something along these lines.

=IF(COUNTIF(\$C\$2:C2,C2)>1,"",MIN(IF(\$C\$2:\$C\$21=C2, \$I\$2:\$I\$21))). This is an array formula, CTRL + SHIFT+ ENTER to commit and copy down. You can also format the cells as "[h]:mm:ss;;;"
 traveler911 Posts: 6, Reputation: 1 New Member #3 Nov 5, 2013, 09:10 AM
I wasn't able to get that to work either. It just came up with an error. I tried to tweak it, but I still was unable to get it to work.

Here is another look at the sheet, and it shows the lookup, and vlookup I tried, as well as the layout on the main page.

Kent
Attached Files
3. Book1.xls (28.5 KB, 141 views)
4.  jakester Posts: 582, Reputation: 165 Senior Member #4 Nov 5, 2013, 06:47 PM
traveler - try this:

=MIN(IF(\$B\$2:\$B\$11=B2,\$I\$2:\$I\$11))

This is an array formula so when you type in the formula, you'll need to hit CTRL,SHIFT,ENTER at the same time to put {} around the formula. You'll need to adjust the ranges to fit accordingly.
 traveler911 Posts: 6, Reputation: 1 New Member #5 Nov 6, 2013, 02:15 PM
I can get that to work while testing on the same page, but when I apply it to the main page, and reference the data page it does not work.

=MIN(IF('Race'!C2:C11=C38,'Race'!I2:I21))

Any thoughts?

Kent
 jakester Posts: 582, Reputation: 165 Senior Member #6 Nov 6, 2013, 02:37 PM
Hmmm... I tested it out on another page and referenced the Race sheet and it worked. Possible solutions:

1. you don't need to enclose Race in single quotes because the exclamation point signifies it it referencing another sheet.

2. is the C38 reference in your formula referring to an actual race type? Make sure that reference is legit.

3. you may have to use the ctrl, shift, enter a couple of times... arrays are tricky.
 Kevin-Radstock Posts: 8, Reputation: 3 New Member #7 Nov 9, 2013, 12:37 AM
Where is the error! See the attached
Attached Files
5. Where is the error!.xls (76.0 KB, 114 views)

 Question Tools Search this Question Search this Question: Advanced Search

## Check out some similar questions!

We get result from the using vlookup but after that I need to go back using the hyperlink on that cell from the results are return.

Vlookup and hlookup [ 1 Answers ]

I want to prafare company payroll where hundreds of employees are working as labours and staff

Vlookup what does that mean. [ 3 Answers ]

Can any one tellme.. what does vlookup formula mean in excel...

Vlookup formula [ 2 Answers ]

I am currently using MS-Excel 2003. Having a terrible time with Vlookup. The concept seems easy enough – but I can’t get it to work with just 2 spreadsheets – and pulling only 1 column from the other spreadsheet. I have these fields on one spreadsheet. Worksheet Tab 1 Worksheet Tab 2...