+ Reply to Thread
Results 1 to 8 of 8

Index match or aggregate (?)

  1. #1
    Registered User
    Join Date
    02-21-2021
    Location
    Manila
    MS-Off Ver
    2019
    Posts
    43

    Index match or aggregate (?)

    Hi All,

    I was hoping someone wouldn't mind taking a quick look at the attached file.

    Goal: Formulas in E3 and F3 copied down.

    I thought I would be able to come up with individual formulas, such as, with the help of this forum, extracting values in L to N into F6 to F8 by using the following formula

    =INDEX(INDEX($L$3:$N$14, MATCH(INDEX($B$3:$B$26, MATCH($E$3,$A$3:$A$26,0)),$K$3:$K$14,0),),AGGREGATE(15,6,COLUMN($L$2:$N$2)-COLUMN($L$2)+1/($L$2:$N$2<>""),ROWS(F$6:F6)))

    ..and sorting out how to retrieve other values myself, for example in F3

    =IF(E3<>"",INDEX($B$3:$B$26,MATCH(E3,$A$3:$A$26,0)), "")

    ...and using COUNTA to get values for Column E, then combining all of the above, and figuring out what to adjust and how they can be copied down, to achieve the desired results.

    But I am having a bit of trouble retrieving values in H.
    And so far the formula in F6:F8 only works because it uses an absolute reference $E$3 for its lookup value.
    Am I close with my attempts?

    I appreciate the help.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,810

    Re: Index match or aggregate (?)

    I would seriously consider redesigning your input table to have components as columns rather use complex formulae to sort out bad data tables.

    I am not familar with the product but look at Power Query /Get & Transform ( in 2019) as a possible solution.
    Last edited by JohnTopley; 03-15-2021 at 10:20 AM.

  3. #3
    Registered User
    Join Date
    02-21-2021
    Location
    Manila
    MS-Off Ver
    2019
    Posts
    43

    Re: Index match or aggregate (?)

    Hi John,

    Thank you for the fast response and the product recommendation. At the moment, I wish to stick to a formula solution.

    I initially considered a separate column for the components, but decided against it, thinking that a single column would be more readable to those who would use the file.

    I've updated the layout just as you suggested. It now has a separate components column. Please have a look at the latest attachment.

    Would the redesign lend itself to a simpler formula now, or did I miss anything?
    Attached Files Attached Files
    Last edited by aswethink; 03-15-2021 at 09:12 AM.

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    33,988

    Re: Index match or aggregate (?)

    I quite like big formulae... but I wouldn't have a clue where to start with that...
    Glenn



  5. #5
    Registered User
    Join Date
    02-21-2021
    Location
    Manila
    MS-Off Ver
    2019
    Posts
    43

    Re: Index match or aggregate (?)

    Hi Glenn,

    Good to see you! I attempted to add a helper table. I thought maybe extracting the values into said table first, using the formula you gave in my previous post (*), might make it easier to come up with formulae for the target cells in yellow.

    (*) https://www.excelforum.com/excel-for...-criteria.html
    Attached Files Attached Files

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    33,988

    Re: Index match or aggregate (?)

    I'll go away and do something else for a bit... and think about this... How much do you love your layout? If needed, would you be prepared

    a) for me to throw it all (or nearly all) out?

    b) to have the results in a single ROW per product on the production sheet.... so extending out to the right.

  7. #7
    Registered User
    Join Date
    02-21-2021
    Location
    Manila
    MS-Off Ver
    2019
    Posts
    43

    Re: Index match or aggregate (?)

    Hi, Glenn. Sorry for the late response. I had to go away from the screen and do something.

    The production sheet aside from listing the components for each product has other information tied to each component, such as notes (or how the certain component will be processed), batch number, allocation, cost, total and availability of component.

    To the right of the table in the production sheet (not in the attachment) are several other columns that pertain to operations for each manufacturing order. Currently, this section also has a multi-row design

    So it might be difficult to place the results in a single row. To give you a full picture, I have updated the sample file.

    The products sheet, aside from country of origin and value, has also other information in rows for each component such as loss%, quantity in and out.

    I am not attached to the layout. But given the above, it seems there is very little room for adjustments.

    And the adjustments that I think can be had are limited to separating product items and their respective components into two columns in the products sheet.
    Attached Files Attached Files
    Last edited by aswethink; 03-15-2021 at 11:14 AM.

  8. #8
    Registered User
    Join Date
    02-21-2021
    Location
    Manila
    MS-Off Ver
    2019
    Posts
    43

    Re: Index match or aggregate (?)

    Hi, Glenn.

    I made a mistake. When you suggested a single row per product I don't know why I thought of COLUMNS. That's why I posted #7.

    I just realised the suggestion was exactly how I organised the helper cells towards the right of the attachment (is it?). If yes, I am okay with it since product components will only ever be no more than 10.

    Need help please.

    Or if not a formula could this be done using VBA, can I mark this thread as solved and post it under the Macros forum to avoid duplicate posts?
    Attached Files Attached Files
    Last edited by aswethink; 03-16-2021 at 11:29 AM.

+ 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] Match Multiple Criteria INDEX & Aggregate
    By remyte in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-05-2019, 01:03 PM
  2. Replies: 1
    Last Post: 08-17-2019, 01:11 PM
  3. Convert INDEX MATCH AGGREGATE FORMULA to VBA code
    By samuelkmh in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-05-2019, 03:28 AM
  4. Dynamic Sort via Index/Match/Aggregate
    By datbigdog in forum Excel General
    Replies: 9
    Last Post: 06-15-2019, 09:02 PM
  5. [SOLVED] Match Multiple Criteria INDEX & Aggregate
    By remyte in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-26-2019, 09:15 AM
  6. [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
  7. [SOLVED] Index/Aggregate/row match with conditions, pivot summary replacement
    By Shruder in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-19-2018, 08:36 AM

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