Find product expiration dates after subtracting sales from purchase orders.

[Stubrok]

I'm trying to figure out a generic (albeit flawed) way of manual lot coding. In the enclosed spreadsheet, we have 2 tables, sales and purchase orders. Based on the info within them I'm trying to display (for each item) the next product expiration date....meaning product that's expiring first. Further explanation enclosed. I hope this makes sense, thanks in advance for any help.

[JieJenn]

Try

=IF(MAX(IF($AA$5:$AA$18=AG5,$AE$5:$AE$18))=0,"",MAX(IF($AA$5:$AA$18=AG5,$AE$5:$AE$18)))

Enter with Ctrl + Shift + Enter

[Stubrok]

Thanks for the quick response....I should have explained better (and highlighted the correct answer cells!). Alright that formula looks good but for what I'm trying to do MIN would fit better. However, I need the formula to check the Balance column (AJ)..and possibly the receipt date column (Y) to return the "smallest" expiration date that is greater than the balance. That's the part I did not explain properly. For example:

Item#2535 - 192 were received on 1/5/12 that expire on 2/29/12 (row 5) and 96 on 1/13/12 that expire on 4/14/12 (row 11). We sold 240 during that time leaving us with a balance of 48 ((192+96) - 240 = 48). See AH5-AJ5. However, we sold the 192 that we received on 1/5/12 first (first in, first out)...then sold 48 of the 96 received on 1/13/12. Therefore the "smallest" date should be 4/17/12 instead of 2/29/12(48<96). If, on the other hand we only sold 50 as opposed to 240 then we would have a balance of 238, leaving the "smallest" expiration date at 2/29/12 (238>96 but 238<(96+192)). I corrected and re-attached the sheet, hopefully making more sense in the process!