+ Reply to Thread
Results 1 to 2 of 2

Excel 2008 : Lookup same value, different returns

  1. #1
    Registered User
    Join Date
    05-20-2010
    Location
    New York
    MS-Off Ver
    Excel:mac 2008
    Posts
    5

    Lookup same value, different returns

    Hi-
    I had previously received some great advice about how to properly parse my data to get it from the interwebs into the excel.
    I now need help with my sorting of the data. The story is this:
    I am in a fantasy baseball league where every week, we accumulate our total stats and go to head to head with another team. At the end of the week, in each matchup, whichever team has the higher total gets a point. There are 12 categories, so you could win a weekly matchup 12-0-0 (wins, losses, ties) if you do really well.
    The problem that I have with this method of scoring is that you only go up against one opponent, not the entire league each week. So I am trying to get the raw stats for each team for each week and say where they finish in each category (1-10).
    I have so far accomplished getting the stats from the internet to the excel, getting each team's weekly totals all in a nice readable order, getting all the stats for week 1 into a nice little box (the blue box on the analysis tab), and I am starting to get the stats sorted to the point that for each category, it spits out the team with the highest value and next highest and all that (the pink box).
    The problem that I am having is that I sometimes get duplicate values: in week 1, both the rockford peachz and the vaya con bernabe teams hit 12 homers, so when I do a vlookup on the "large" function, it gives the same team for the second highest homers. Is there a way to get around this? Do I need some insane complicated if function?
    I have attached the spreadsheet, all of the real action is in the analysis tab. Also, FYI, we are currently in week 8, so the only stats that I have are before that.
    thanks!
    Attached Files Attached Files
    Last edited by washkow; 05-24-2010 at 04:04 PM.

  2. #2
    Forum Contributor
    Join Date
    08-11-2009
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    149

    Re: Lookup same value, different returns

    Just checking on unanswered threads and while I don't know the answer I thought it might help to restate your question....

    You're using a combination of VLOOKUP and LARGE to find and rank teams. The problem is when there's a tie, LARGE returns the first occurance and you don't get the name of the 2nd team in the tie. You've created an example of this in R29:AE38 (red bordered) based on the data directly above it.
    Question is how do you make it rank the teams and properly list the 2nd (or more) teams in a tie?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1