# To Number Required

##### Active Member
Respected All,

I Have a Excel sheet of Pick list Order Details which consist the Details of TO number, Article and respective Qty against To and Article number
Based on the Excel or Pick list my assign people go and get the article and Qty and keep it one carton
Now I want a formula that can allow me to get the TO number based on the Articles scanning in my scanning Template
kindly find the Excel attachment along with the required format

regards
Naresh

#### Attachments

• 10.1 KB Views: 8

#### bosco_yip

##### Excel Ninja
Try,

In J4, formula copied down :

=INDEX(LOOKUP(ROW(INDIRECT("1:"&SUM(\$C\$3:\$C\$11))),SUMIF(OFFSET(\$C\$2,,,ROW(\$1:\$12),),"<>")+1,\$A\$3:\$A\$11),AGGREGATE(15,6,ROW(INDIRECT("1:"&SUM(\$C\$3:\$C\$11)))/(LOOKUP(ROW(INDIRECT("1:"&SUM(\$C\$3:\$C\$11))),SUMIF(OFFSET(\$C\$2,,,ROW(\$1:\$12),),"<>")+1,\$B\$3:\$B\$11)=G4),COUNTIF(G\$4:G4,G4)))

##### Active Member
Respected Bosco Sir,

Sorry for Late Reply and thank you so much for giving your time to solve my query
your Suggested formula giving me the same result which was required to me but I have to scratch my head a lot to understand
thank you so much once again sir

regards
Naresh

#### shrivallabha

##### Excel Ninja
If you are okay with helper columns then simpler approach can be used.

Helper#1 Calculate cumulative running total for pick orders
=SUMIF(\$B\$3:B3,B3,\$C\$3:C3)
Helper#2 Calculate cumulative running total for scanning template
=SUMIF(\$G\$4:G4,G4,\$H\$4:H4)
Helper#3 Based on Helper#2 Qty identify the Pick order basket using Helper#1
=MIN(IF(\$B\$3:\$B\$11=G4,IF(\$D\$3:\$D\$11>=K4,\$D\$3:\$D\$11,SUM(\$H\$4:\$H\$25)),SUM(\$H\$4:\$H\$25)))
Finally use a LOOKUP to get the reference Pick Order basket.
=LOOKUP(2,1/(G4&L4=\$B\$3:\$B\$11&\$D\$3:\$D\$11),\$A\$3:\$A\$11)

See attached file for demonstration.

#### Attachments

• 13.1 KB Views: 3

#### Peter Bartholomew

##### Well-Known Member
Pretty much the same I think but with structured references and XLOOKUP.