# Index match or aggregate (?)

1. ## 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.

2. ## 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.

3. ## 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?

4. ## Re: Index match or aggregate (?)

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

5. ## 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

6. ## 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. ## 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.

8. ## 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.

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?

##### Users Browsing this Thread

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

#### 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