Lookup multiple instances of value in ws2 and return data to WS1

Hi all,

My first post so please be gentle, my terminology may be off and i am certainly no excel expert, apologies if i overcomplicate a process which you find simple!!

I am attempting to create an invoice on WS1 that will autofill with data from WS2 and using an order id as lookup value..

each item from a single order is on a separate row in WS2 with an identicle Order_id in column 1 so VLOOKUP only returns the first instance of the value, i gather that INDEX needs to be used in some way but i am not fluent enough in excel to work out how to do this, please help!

Simplified workbook is attached
invoice example.xlsx

on WS2 there is three rows with the same order id in column 1, i need to reference these rows on WS1 using the same order id as lookup value

i have used a VLOOKUP for the first row in the invoice to illustrate what it should look like but obviously VLOOKUP only returns the first instance of the value so i need to know the formula to return each row with that instance on separate rows on WS1

Hope this makes sense, apologies for the lack of proper terminology or incorrect terminology if applicable!!

Please help before i tear out my own eyeballs in frustration

Hi Fotis,

Many thanks for the fast response, this is exactly what i needed.

As mentioned i am a noob though, would you mind explaining the formula in simple terms?

I need to now edit the formula to work with the actual worksheets i am using but cannot publicly post these due to sensitive customer info, if you are able to make me understand how these functions actually work i would really appreciate it.

If it's too much to ask then thanks very much for the help you have already provided, i'll figure it out with a bit of tinkering i'm sure!