I have a spreadsheet running that shows all outgoing stock in units, the problem is the stock comes in different pack sizes and needs to be charged for the number of picks that have been done to complete the order.



what I need is when 6 units is entered into excel it returns what i would have to pick to get those units i.e 1 full box of 4 and 2 inner's = 6 units as an example.

i have a DATA sheet that holds all the pack sizes and the spreadsheet can VLOOKUP these however it will only return the pack size not the pick Qty.