Index/Match/Array based on Values - complicated problem =(

Hello - first post here and hopefully someone might be able to help me a little =)

This problem is indeed complicated from my perspective at least. Per my example, I have a list of categories (colors in the file) which break down a list of codes. This list of categories never changes from time to time. Secondly I have receive data on a monthly basis with an output, also with a code attached.

What I would like to accomplish is to create tables (on a monthly basis) which break down all the outputs categorized by the categories (colors) and then subcategorized by the codes. Every month I receive several thousand outputs to play with.

I think a couple things makes this complicated. First Several outputs may have the same code, which presents problems from a =match perspective which only reads the first value it finds. Second, I'm categorizing ultimately based on the colors which are only linked to the outputs by the codes (seems like a two step ordeal). I fear this involves array formulas, sumproducts, and many other crazy tools I am not good enough at!

Is anyone able to help me with this?? If so I'll be absolutely grateful!! Thank you!

That's easy, just do a custom sort. In the table, click on a drop down arrow, sort by color, custom.

Add a level.
The first level would be the Categories column the 2nd level Codes.

Make sense?

I'm afraid I don't understand.
What do you want to do with the data? Create a table for each category?

Can you make a sample spreadsheet? Change sensitive info?

Okay see this spreadsheet to see if it can clear it up.

The first tab (categories) describes which codes apply to each of the categories. This data never changes from time to time.

The second tab (codes) is what I receive on a monthly basis. It spits out different values (outputs) which are all unique and also assign the correct code to each of them. So at this point you can connect the outputs via the codes, to the categories.

The third tab (output tables) is where I list each of the outputs broken down by categories. So kinda my thought process here is:
- First I need to determine which codes apply to each category. As you see in the file the cells are linked, so I won't be hardcoding these.
- Secondly I need to figure out which outputs correspond to the codes for each category. This is also a tricky part because you can see there may be multiple outputs for a single code, which means you probably couldn't do a =match based on the code number because it wouldn't detect the other outputs for that code number.

Does this clear anything up? Please let me know if there are still any questions and thanks!

Have a look at Output Tables columns E & F.

Will that work?

Thanks so much! This is perfect!