Results 1 to 31 of 31

Excel 2007 : Using INDEX and MATCH (or other) to return cell based on largest associated value

Threaded View

  1. #1
    Valued Forum Contributor
    Join Date
    06-10-2011
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    341

    Using INDEX and MATCH (or other) to return cell based on largest associated value

    Hi,

    I am having trouble with using INDEX and MATCH (open to using other formulae too) to tell me the cell based on the largest value in a table.

    My WIP formula:
    =INDEX(Score!A3:AU813,MATCH(LARGE(Score!B3:AU813,1),Score!A:A,0),MATCH(J2,Score!B1:AU1,0))
    I think the problem is there, though I am not sure about that.

    Basically I want to find the highest value in a single column between Score!B3 and Score!AU813 with the column based off Output!J2 (current sheet) and give me the cell value of Score!A3:A813.

    Example, I enter 73 for J2 in the Output sheet and it should use column AN in the Score sheet to check for the largest value (ignoring the first 2 rows which are background codes) and return the row of that largest value and use the value in column A of that row as the result.

    As it is possible to have multiple same largest values, how would I handle those best on my Output sheet? I am looking at the 5 largest values so I guess there is room for 5 same top values. So on the Output sheet is it possible to add a handler to the 2nd-5th row under "Outcome" to ignore/skip the value of the fields above?

    Example: If A6 = A5, go to next highest value in the score sheet and give me that instead.


    Worksheet attached.
    Attachment 131737

    Thanks!
    Last edited by dip11; 12-15-2011 at 01:21 PM. Reason: Thank you Marcol and Haseeb!

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