Index/Aggregate/VLOOKUP Across Tables Issue

I have a situation in which I need to perform a variety of sorting, possible indexing, looking up one value and returning another, etc., all in the same formula. (For an extremely basic novice as myself, this is daunting and I've already wasted many, many hours trying to figure it out on my own.)

Synopsis:
I have a workbook used to enter & process orders from customer POs. This is on a worksheet that does a large number of things, but I'm isolating just the part I'm having an issue with to this post and in the attachment. I've set it up so that Data Validation is used to create a drop down list of Products available to enter on each line of the Order section, range C5:H19 in the attachment. Then the Quantity is entered in a separate cell. Makes sense so far, right? Except there are a couple of snags.

1) I need the items to appear in the same order they're in on another table in the workbook ("Products" tab in the attachment and actual working file). This has a clumsy but effective solution implemented for the Order table of the worksheet by creating a macro that selects the data in C5:H19 and then sorting, which isn't ideal, but is the only sort-in-place function I could get to work without crashing Excel or doing other nasty things. So, this part works okay. The problem comes in when I try to use that data to populate another table with values based on whether line items are "flagged" or not.

2) The drop down list for selecting items being ordered contains a string of various information related to a product so that no matter what piece of information a user begins to enter, it finds a match for that item if one exists. This is helpful for instances where customers will place an order just based on item description or UPC/GTIN instead of using our actual product numbers. But this is creating an issue elsewhere, especially when it comes to the second part of #1 above. However, this data needs parsed onto another table that will get emailed to a team for review, and it has to separate out the flagged items from the not-flagged items by putting them in their respective columns. I don't want the huge string of text from C5:C19 to populate the item numbers in I9:I21 and K9:K21 - I want it to match what's in C to what's in column H of the Products tab and return the value in Column A, instead. And, of course, I still need results to appear under the correct headings (flagged/not flagged and quantities), while also aggregating any blank rows left as a result of this sorting process.

The attached file provides much more extensive notes on the "Order Entry" tab in the interest of keeping this post erring on the side of brevity, if it's not already too late for that...

If anyone can help me figure out what formulas need to go in I9:L21 in the attached example to achieve the desired results, I'd be eternally grateful! If there is a more elegant solution to how this is set up currently, I'm also open to suggestions!

Thanks in advance,

-B

Order Entry Issues.xlsx