Calculate number of days between dates using IF,INDEX,MATCH functions

Question

I have a complicated case here.

I am working on an inventory stock list, basically I want the Column "Days of Storage" to show the number of days the inventory sit in the warehouse.

I have already come up with the formula for "Out" of inventory using IF, INDEX AND MATCH functions, but not for "In" inventory. I want the column for "Days of Storage" for "In" to have the same number of days of storage as the "Out" of inventory.

Do note that if the stock is sold, there will be a corresponding "Out" linked by the "index number". When the stock is not sold, there is no corresponding "Out" because the inventory is still in the warehouse, wondering what formula would work for this. Really appreciate your help and please clarify with me if the question is confusing. Thanks!

Answer 1

Have a try on below formula.

=IFERROR(INDEX($B$2:$B$5,MATCH(D2,$D$2:$D$5,0))-INDEX($B$2:$B$5,MATCH(D2,$C$2:$C$5,0)),"")