Q:

Use one lookup to generate multiple values

I have an array filled with product orders that have multiple products. 
Order #    Product
N-122      C135
N-122      P223
N-122      C134
N-134      P223

I have seen other threads merge products into one row that have the same order.
Excel Macro - Rows to Comma Separated Cells (Preserve/Aggregate Column)
Is there a way to lookup my order values without merging the products into one row?
I want to create a table in another sheet that once the Order # is input it will show all of the products associated with it on multiple rows. 
Order #     Product
N-122       C135
            P223
            C134

A:

As Andre mentioned you can use a Pivot Table but if you still want to do a formula then you can do this
=IFERROR(INDEX($A$2:$B$5,SMALL(IF($A$2:$A$5=$D$1,ROW($A$2:$A$5)-1),ROW(A1)),2),"")
Note this is an Array formula and you have to press CTRL+SHIFT+ENTER
See this screenshot

Once you enter the formula in E1, simply drag it down to find matches.

