How can I use INDEX and MATCH along with conditions to filter data into different tables?

Question

I believe there is a simple solution here that I am just not grasping. This spreadsheet needs to be able to dynamically filter data "graphically" anytime a new company is put in the left table (including its category and quantity).

See attached samples as well as my "before" and "after" examples of what I am trying to do. Essentially, I'd like the current INDEX & MATCH formula in G, J, and M columns to first check if the corresponding data is categorized as Small, Medium, or Large, then to do the INDEX & MATCH if TRUE and ignore other data. This would create three separated vertical tables categorized as Small, Medium, or Large and only include companies that match those categories.

Thanks a lot for any ideas. I have shared the sample spreadsheet but set it to prompt you to "make a copy" so all can have access to original sample to play with. Let me know if I didn't do that correctly and I'll fix it.

As for what I have already tried, all attempts did not correctly filter out data. There seems to be a correct layering of the order of operations that is necessary to ensure first the correct data is selected and then it is arranged correctly on the right side. It's almost as if I need two nested INDEX & MATCH formulas since the data in the table will change dynamically as more lines are filled in.

The likely formula that will need adjusting to do this is found at the top of each table on the right. Here's what I have so far that works (but does not filter by category first)

=IFERROR(INDEX($B$2:$B,MATCH(F2,$C$2:$C,0)),"")

My current spreadsheet that does NOT correctly filter data on the right side

This is how I want the data to be dynamically filtered as more customers are added to the left table

This is a link to the spreadsheet. It should ask you make your own copy to work with after you sign in to your Google account

Answer 1

Use Filter

You may use filter to get the result and address the issue where two customers have the same size and quantity.

=BYROW(L2:L,lambda(x,JOIN(", ",IFERROR(INDEX(FILTER(A2:C,A2:A="Small",C2:C=x),,2)))))

Output:

I added a test entry to test multiple similar entries.