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.

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?

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

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.

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.

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?