Index/Match multiple hits of same cell value and add rows to fit spill

[kidneythief]

Hi guys! Not sure how to approach this - with lookup and aggregate, an index/match sub or something else.

So In the sheet called Empty, when B2 is filled, it populates the Invoice column in D4:D with unique invoice numbers from an external order sheet.
The Sales sheet has a list of orders where each invoice is broken down per item (so each single invoice number in the Empty sheet can have multiple instances
in the Sales sheet).

I need to pull data from the Sales sheet into the Empty sheet using the invoice numbers as a reference. However, since the Empty sheet only pulls a single
instance of each invoice number, I need to manage spillover and insert rows when needed, as seen in the Filled sheet.

Going further, I was wondering if it would be possible to transpose output into something like what's shown in the Filled_Alt sheet, where Name and Invoice
(A2 and D2) are not repeated, and an extra row is added where the Price column items (or any additonal columns added there like quantity, etc.) are added up.

Ideally, the whole sheet would be populated as in Filled_Alt automatically each time B2 is changed and a new set of invoice numbers populate the D column.

Sorry to be asking so much, and I'd be really grateful for any help or guidance on this.

 

[fjns]

Hi, I did not use array formulas in the tables I created for you.

 

[kidneythief]

Hi, I did not use array formulas in the tables I created for you.

Thank you very much, that did the trick! May I also ask, so I understand, can the hashtag be any arbitrary symbol appended to the invoice number to make exact matches?

 

[fjns]

Yes, can the hashtag be any arbitrary symbol. It is better not to use periods, commas or semicolons.