Ask Experts Questions for FREE Help !
Ask
    traveler911's Avatar
    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. File Type: xls Book1.xls (36.5 KB, 112 views)
  2. Kevin-Radstock's Avatar
    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's Avatar
    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. File Type: xls Book1.xls (28.5 KB, 172 views)
  4. jakester's Avatar
    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's Avatar
    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's Avatar
    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's Avatar
    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. File Type: xls Where is the error!.xls (76.0 KB, 176 views)

Not your question? Ask your question View similar questions

 

Question Tools Search this Question
Search this Question:

Advanced Search

Add your answer here.


Check out some similar questions!

How to use hyperlink and vlookup [ 1 Answers ]

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...


View more questions Search