Trying to understand index,match better to allocate inventory

Question

Please help: My brain hurts from trying to figure this one out. I am trying to use a table that includes the Part # (Row 1), along with the Lot # (Column A). The Array has different quantities for the different Lot Numbers.

The problem that I am encountering is that I need to sort the lot numbers in descending order as they correspond to manufacture date. We want to allocate the oldest stock first. I am using 2 sheets in google sheets, the first called: "Order Data" pulls in new orders from the website, the second called: "Inventory" keeps track of the quantity of each lot number that has not been allocated to an order.

Here is a sample of the 2 sheets: (Inventory)

Lot#    TIP2006020  Allocated   TIP25   Allocated   TIP3006005  Allocated   TIP3006010  Allocated   TIP4006005  Allocated   TIP4006010  Allocated
160311                          1                   
160408          11                                  
160504          324             12                  
160601          9               97      1           
160706          163             213                 
160801  25                      158                 
160901                  3       113             1   
161101  59                              13      5   
161209                                  322     113 
161223                                  83      8   

(Order Data)

Order # Item#   Quantity    Lot# Allocated
Test    TIP2006021  1       (This is the look up value)

Does anyone have an idea of how I can allocate the inventory based on a lookup rather than nested if statements?

Thanks in advance for any ideas...

Answer 1

I'm assuming that after you've used all items from a particular lot, you want the formula to return the next lot number with unallocated items. I'm assuming that as you use items from a lot, you just increase the number in the Allocated column. So, to identify where a lot has remaining items, we need to check where the number of allocated items is less than the original number of items in the lot.

I'm also assuming your columns are in a fixed order in your Inventory table:

Lot#, Count of Item1 in Lot#, Count of Allocated Item1 from Lot#, Count of Item 2 in Lot#, Count of Allocated Item2 from Lot#,...

If these assumptions are incorrect, please clarify further how you use the Inventory spreadsheet to track your inventory as items are allocated.

Otherwise, this formula below should work. (Let me know if it doesn't.) To make this formula handle any size of data, you need to first convert your data to a table:

1. Select the Lot# field
2. Go to Insert tab on the ribbon and click Table
3. Make sure "My table has headers" is ticked and click OK
4. On the Table Tools > Design tab of the ribbon, at the far left change the Table Name to Inventory.

This formula then finds the next Lot number with available inventory for an Item number specified in cell A1:

=INDEX(Inventory[Lot'#],MATCH(TRUE,OFFSET(Inventory[Lot'#],0,MATCH(A1,Inventory[#Headers],0)-1)-OFFSET(Inventory[Lot'#],0,MATCH(A1,Inventory[#Headers],0))>0,0))

Note: This is an array formula. To enter is correctly, you need to double click into a cell, paste the formula, then save it by pressing CTRL+SHIFT+ENTER. You need to do this every time you edit the formula (which should only be to change the cell reference of the Item number)

Answer 2

Does anyone have an idea of how I can allocate the inventory based on a lookup rather than nested if statements?

I mocked up something similar and used the following formula in C15:

=INDEX($A$2:$A$11,MATCH(B15,INDEX($B$2:$D$11,,MATCH(A15,$B$1:$D$1,0)),0))