Nested Sumifs Offset Index Match Match

Hi folks,

I'm having trouble wrapping my head around this.
Excel 2010, Windows 10

I have a Table TC (type charges)
That table contains various type charges (ex. Delivery, Fuel, Additional Pieces) sorted by Order

Unfortunately, the type charges are not in fixed columns.
Each type charge has 4 columns of data:
ID (Column Header is CLCID#)
Name (Column Header is CL TYPECHG#)
Rate (Column Header is CL RATE#)
Quantity (Column Header is CL QTY#)

Each Order will have multiple type charges

I want to:
Index TC,All
Match Order (Appears in formula as "Carrier Invoice No") to TC,Order
Match ID ex: 739
Offset right 2 columns to get Rate
Multiply Rate by Quantity

Currently, I'm using an excessively long formula to search every possible column (see attached column D for working formula)

I've hit a wall on this one. Any help is greatly appreciated.

Hey CK,

Thanks for the example
How did you break the orders into separate rows on the TC worksheet? Was this done manually or is there a function to automate it?


Additional Details for Clarification:
The TC data is retrieved from our ERP system and may contain 200+ unique Orders with 6+ type charges per Order.
I've asked our President and Head of IT to restructure our type charges to be fixed columns per type charge but they refused

Unfortunately, multiple worksheets are required to conform to the Client's billing template & system formatting requirements.
Apparently we'll be switching to EDI so eventually it will be our IT department's problem. No projected effective date at this time though.


(Also, that Heinlein quote is 100% haha)

oh, neat. My VBA knowledge is pretty limited. I'll do some research about Power Query.
thanks for all the help, CK