Results 1 to 5 of 5

INDEX, MATCH & LARGE trouble with duplicate values

Threaded View

paulstuartbullock INDEX, MATCH & LARGE trouble... 07-12-2015, 05:04 PM
azumi Re: INDEX, MATCH & LARGE... 07-13-2015, 12:18 AM
azumi Re: INDEX, MATCH & LARGE... 08-04-2015, 08:37 PM
Tony Valko Re: INDEX, MATCH & LARGE... 08-04-2015, 09:19 PM
azumi Re: INDEX, MATCH & LARGE... 08-04-2015, 11:17 PM
  1. #1
    Registered User
    Join Date
    04-04-2013
    Location
    london, england
    MS-Off Ver
    Excel 2010
    Posts
    14

    INDEX, MATCH & LARGE trouble with duplicate values

    Hi, I'm working on a student tracking sheet where students have various grades in different subjects. I'm using 'LARGE' to identify their best grades within a selection of subjects, but I need a way of pulling the column reference to accompany a given cell and locate this in the adjacent cell.

    First group of subject titles currently B7:I7
    First group of grades B8:I8
    Second group of subjects in Q:AV, including 4th-7th highest from first group.

    Currently using:
    K8=LARGE(B8:G8,1) and similar for the second and third highest values.
    J8=INDEX($B$7:$I$7,1,MATCH(K8,$B$8:$I$8,0)) to pull the column titles (Subject names) out.

    That is, if a student has five grades say in Maths (7), English (5), French (4), Geography (9) and History (8), and I wish to identify not only the top 3 grades, but also the subjects of those grades and have them reported elsewhere: 9, Geography, 8 History, 7 Maths. It delivers this.

    There is a small catch here, I am in the position where I need to take the first three scores and corresponding subjects from one grouping, and the first three from another grouping (Which includes 4th highest onwards from the first group).

    I ultimately need these six scores, plus the corresponding subjects to be output somewhere.

    The example here:
    Science, Double Sci, Triple Sci, Geog, Hist, French
    4, 6, 2, 3, 7, 3
    has several subjects with an equal score (Perfectly likely), but my formulae only pick up the first value they find and output this twice when this happens,

    S8=LARGE(B8:I8, 4)
    R8=INDEX($B$7:$I$7,1,MATCH(S8,$B$8:$I$8,0))

    S8 returns "Geog", but the same set up for 5th largest also returns "Geog" rather than "French".

    How can I get it to distinguish between these duplicate values, take the first one as a first choice value and then 'see' the second duplicate value? Also a little unsure if my use of LARGE 4/5/6/7 is the best solution to identify the lower scores in the first group.
    All suggestions greatly appreciated.
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Index Match with Duplicate Values
    By js2178a in forum Excel General
    Replies: 2
    Last Post: 09-28-2014, 02:21 AM
  2. [SOLVED] Problem Using LARGE formula with Index/Match to pull values when there are Duplicates
    By BDavis in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 08-11-2014, 02:23 PM
  3. [SOLVED] Help with Index/Match/Large problem with duplicate values
    By enphynity in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-31-2013, 09:56 AM
  4. [SOLVED] Index/Match/Large Duplication of items; not returning true values
    By Darren_Rix in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-16-2013, 07:27 AM
  5. Index, Match, Large Function returning duplicate names
    By RNeel55 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-08-2013, 04:09 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