+ Reply to Thread
Results 1 to 2 of 2

Using index and match to find several values

  1. #1
    Registered User
    Join Date
    12-29-2009
    Location
    Canada
    MS-Off Ver
    Excel 2003
    Posts
    16

    Question Using index and match to find several values

    Hello. I have administered a test for my students where they answered many questions which then give scores to each of 8 areas of "intelligence." I use a formula, INDEX($CS3:$CZ$4,1,MATCH(LARGE(CS3:CZ3,1),$CS3:$CZ3,0)) to search the 8 values and select the largest and return the title of that intelligence which is in the column header, row3.

    My problem, or one of them, is that some students have more than one area of intelligence tied for the highest score. As it stands, I onmly report the area which is the first that Excel finds on the left. I would like to have Excel return all the areas (column titles) for the columns that share the highest scores. If the high score is unique, I'd get the current result. If there is a tie, I'd get 2 results etc.

    Vlookup, index and match are about as advanced as I get with Excel at the moment, but I'd love to learn how to solve this problem. The end goal is to help my entire school program better for our students, so it's a worthy cause.

    Thanks,
    Josh
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    09-20-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    2,278

    Re: Using index and match to find several values

    Hello
    I've attached a copy of your example file with a possible solution using array formulas in the blue shaded area at the end of you score table. I've allowed for 8 possible ties of the max value but if this is unlikely, then you can allow for as many as you wish. These are array formulas and must be entered using Ctrl+Shift+Enter.

    Just noticed that you're using Excel 2003. I thought you were using 2007 as that was the format of your file. The attached file and formula will not work in 2003 as it uses the Iferror function but I can change this to work in 2003. I will load a 2003 version as well.

    Hope this helps.
    Last edited by DBY; 12-19-2011 at 12:18 PM. Reason: Added last paragraph

+ 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