+ Reply to Thread
Results 1 to 8 of 8

ELO ranking lookup problem

  1. #1
    Registered User
    Join Date
    10-03-2012
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    4

    ELO ranking lookup problem

    Hi,

    I'm having an issue creating a lookup on my ELO ratings table
    I'm trying to put a formula in E39 that will look up the value in C39 and compare it to the values in C29:D38, find the equivalent value then pull the value in from the corresponding cell at Z29:AA38
    Additionally, as the sheet gets bigger there will be multiple repeats of values in C29:D38, at the moment C39 is looking for the value SUN, I need to make sure it just pulls the value of the last SUN from the range, starting from the bottom

    thanks for any help
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    05-20-2013
    Location
    Ohio, USA
    MS-Off Ver
    Excel 2013
    Posts
    941

    Re: ELO ranking lookup problem

    In cell E39...

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    10-03-2012
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: ELO ranking lookup problem

    2009 season.xlsx
    Hi Jhren, that nearly works, I pull it down and it works for the first 2 rows, then some errors start creeping in.

    I can't see what's going wrong

  4. #4
    Valued Forum Contributor
    Join Date
    05-20-2013
    Location
    Ohio, USA
    MS-Off Ver
    Excel 2013
    Posts
    941

    Re: ELO ranking lookup problem

    The errors start with E41 because the formula searches C29:D40 for a match to "BIR". There is none. You'll have to add error handling to the formula, but I have no idea how you want the error handled...

    The error in E42 is because the E41 error results in an associated error in Z41...

  5. #5
    Registered User
    Join Date
    10-03-2012
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: ELO ranking lookup problem

    Hi jhren,

    but BIR appears in D37 so it should be pulling the associated value from Z37

  6. #6
    Valued Forum Contributor
    Join Date
    05-20-2013
    Location
    Ohio, USA
    MS-Off Ver
    Excel 2013
    Posts
    941

    Re: ELO ranking lookup problem

    Quote Originally Posted by rugbytrader View Post
    Hi jhren,

    but BIR appears in D37 so it should be pulling the associated value from Z37
    You are correct... my eyes aren't what they used to be.

    Anyway, found the problem. Need to make the MATCH an exact lookup (so horizontal index doesn't need to be sorted)....

    =INDEX($Z$29:$AA38,SUMPRODUCT(MAX(($C$29:$D38=C39)*(ROW(C$29:D38))))-28,MATCH(C39,INDIRECT("C"&SUMPRODUCT(MAX(($C$29:$D38=C39)*(ROW($C$29:$D38))))&":D"&SUMPRODUCT(MAX(($C$29:$D38=C39)*(ROW($C$29:$D38))))),0))

  7. #7
    Registered User
    Join Date
    10-03-2012
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: ELO ranking lookup problem

    jhren, you are an absolute legend!

    thank you so, so much

  8. #8
    Valued Forum Contributor
    Join Date
    05-20-2013
    Location
    Ohio, USA
    MS-Off Ver
    Excel 2013
    Posts
    941

    Re: ELO ranking lookup problem

    Your Welcome...!!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] ranking formula and a double lookup
    By Nafrtiti in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-01-2012, 03:09 PM
  2. Ranking problem
    By robtai in forum Excel General
    Replies: 5
    Last Post: 05-09-2012, 12:38 AM
  3. Number ranking and date lookup
    By THeavyGuy in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 12-04-2009, 03:39 PM
  4. help using lookup and some kind of ranking criteria
    By my in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-18-2006, 12:45 PM
  5. Ranking Problem
    By sa02000 in forum Excel General
    Replies: 2
    Last Post: 02-10-2006, 06:25 PM

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