VLOOKUP & Returning Two Pieces of Data

[meseleto]

Good morning everyone,

I am trying to automate this report and I was able to get the first half completed but i'm stuck on the second half.

I have a list of orders for the day and I have a spreadsheet with inventory of UPCs that need to ship. The first half of the project looked up all these UPCs, checked them against inventory and came up with a shortage/surplus variance.

The second half now needs to return two pieces of information - the quantity short and the time it needs to ship.

For example, on the MB52 tab, line 5 column k (90070247208135) shows a shortage of 15 combos (column N). So I would want column P to show the first shortage and column Q to show the time that first truck should ship that is currently short inventory. It would reference the DockSheet tab and look for the matching UPC code on column J and compare the inventory (column H) to the qty demanded (column I). In this example, row 94 shows a inventory of 0 and a qty of 15 and it ships at 18:30 09/05/2019.

How could I get this accomplished? There could be multiple shortages at different quantities for various times. For example, row 15 on the MB52 tab shows 2 shortages - one for 11 and another for 20, for a total of 31 for two different times, 18:30 09/05/2019 and 01:30 09/06/2019.

Probably confusing, let me know if I need to clarify it.

Thanks all!

[meseleto]

It does not look like VLOOKUP can handle multiple conditions / return multiple matches. I believe I would need to use a combination of INDEX and MATCH formulas to make this work. I'm confused on how to make it work though. Any suggestions?