Snasui Excel Forum

Hi Dear,

I need your help regarding lookup an order woud return name,item, quantity and price.
For example if I input order 1234
It would return Budi and below information

ITEM QTY PRICE
Bag 2 20,000
Book 1 30,000
Book2 1 40,000
Pen 2 50,000


Thanks

Hint:I need formula without using a helper column

Please try with these formulas:

1. L4
   =VLOOKUP(L3,$B$3:$C$15,2,0)
   Enter
2. K7
   =IF(ROWS($J$6:J6)>1+MOD(SMALL(IFERROR(1/(1/(FREQUENCY(IF($A$2:$A$14="",ROW($A$2:$A$14)),IF($A$2:$A$14<>"",ROW($A$2:$A$14)))))+10000*ROW(INDIRECT("1:"&COUNTA($A$2:$A$14)+1)),FALSE),COUNTIF($A$2:INDEX($A$2:$A$14,MATCH($K$2,$A$2:$A$14,0)),"<>")),10000),"",OFFSET(INDEX($C$2:$C$14,MATCH($K$2,$A$2:$A$14,0)),ROWS(J$6:J6)-1,COLUMNS($J6:J6)-1))
   Ctrl+Shift+Enter > Copy to right and down

Thank you