+ Reply to Thread
Results 1 to 7 of 7

Index value with column location determined by aggregate(15,6

  1. #1
    Registered User
    Join Date
    04-09-2020
    Location
    Jakarta
    MS-Off Ver
    O365
    Posts
    5

    Question Index value with column location determined by aggregate(15,6

    Hello,

    I have a table with that populates per score per company

    A1:E1 46, 46, 59, 63, #DIV!/0
    A2:E2 Company 1,Company 2, Company 3, Company 4, BLANK

    I wanted to use INDEX($A$2:$E$2,1,COLUMN(AGGREGATE(15,6,$A$1:$E$1,1))) to ignore errors and find the smallest value, return the respective company name.
    But it does not seem to catch the column number using this formula.

    I want to display this on a separate table where I would call out top 2 and bottom two scorers so I need to make sure it will still work if two companies share the same score.

    What am I doing wrong?
    Attached Files Attached Files
    Last edited by Lordiclaire; 04-09-2020 at 05:46 AM.

  2. #2
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Index value with column location determined by aggregate(15,6

    for sake of illustration -- using the above references / values as source

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

  3. #3
    Registered User
    Join Date
    04-09-2020
    Location
    Jakarta
    MS-Off Ver
    O365
    Posts
    5

    Re: Index value with column location determined by aggregate(15,6

    I had to transpose the table for other reasons so would this work?

    =IFERROR(INDEX($A$1:$A,MOD(AGGREGATE(15,6,(ROUND($B$1:$B$,4)*10^5+ROW($B$1:$B$5)),COLUMN(H$2:H2)),100)),"")

    also there are other values in this sheet so I don't know if I can select the whole column A:A.

  4. #4
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Index value with column location determined by aggregate(15,6

    The INDEX can reference the entire column, the MOD(AGGREGATE is determining the ROW number, and is limited to the relevant rows.

    So, it should be:

    =IFERROR(INDEX($A:$A,MOD(AGGREGATE(15,6,(ROUND($B$1:$B$5,4)*10^5+ROW($B$1:$B$5)),COLUMNS($H2:H2)),100)),"")

    the piece in red I am unclear on -- if you are now looking to return smallest in H2, and 2nd smallest in I2 then use

    COLUMNS($H2:H2)

    if, conversely, you're still looking to return smallest in H2 and 2nd smallest in H3 then persist with

    ROWS(H$2:H2)

    this is just being used to determine "k" (from 1 to n) for use with the AGGREGATE SMALL/LARGE - and should increment by 1 as you drag the formula (to return next value).

    note: in both cases the use of COLUMNS/ROWS rather than COLUMN/ROW.

  5. #5
    Registered User
    Join Date
    04-09-2020
    Location
    Jakarta
    MS-Off Ver
    O365
    Posts
    5

    Re: Index value with column location determined by aggregate(15,6

    Seems that is referencing to the wrong cell when I indexed the whole column. For your reference, I have attached the file I'm working on in the post. The cells in question are marked in red from row 107 onwards.

  6. #6
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Index value with column location determined by aggregate(15,6

    having reviewed your file -- given you're returning the associated value in the adjacent column (something we were not aware of) you can simplify:

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

  7. #7
    Registered User
    Join Date
    04-09-2020
    Location
    Jakarta
    MS-Off Ver
    O365
    Posts
    5

    Re: Index value with column location determined by aggregate(15,6

    Thanks! This works perfectly!

+ 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] Index/Aggregate formula calculates correct row but does not yield name from Index array
    By FivestarMac in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-17-2019, 05:07 PM
  2. Replies: 1
    Last Post: 08-17-2019, 01:11 PM
  3. [SOLVED] Create List - Index Match or Index Aggregate or other?
    By bambamclint in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 02-10-2019, 01:30 PM
  4. [SOLVED] Index match match with the column number determined by two rows???
    By bridge4444 in forum Excel General
    Replies: 2
    Last Post: 08-08-2016, 04:23 AM
  5. Index, Aggregate, Row - Removing Duplicate W/ Index Help
    By adbexcel in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 01-04-2016, 03:53 PM
  6. [SOLVED] Simple: Paste range to location determined by IF statement
    By Geoff. in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 11-27-2014, 07:23 PM
  7. Dynamic Index/Match function when column location is unknown?
    By ac1987 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-10-2014, 09:47 PM

Tags for this Thread

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