2D lookup with multiple results

[mick0005]

Hi all - long time listener, first time caller. Just kidding, I used to be active around here a few years ago and do my best to answer questions when I could in my spare time; I enjoyed it. I've been in a role that used excel a lot less so haven't been on in awhile.

I am trying to return multiple results from a 2d lookup. I've managed to get a single result using an array formula to do a 2 dimensional matrix lookup successfully, however, I have multiple results I am looking for and can't figure that out.

What I have here is a shipping schedule. I have a many columns (H-BZ) on the "Production Shipping Plannin" wksht that captures the quantities shipping within each container by date for a given item. It is possible (and certain for many items) that there are multiple containers shipping on the same date for the same item and these will appear on multiple columns. I need a way to capture the aggregate quantity shipped by item, by container, by date to aggregate inbound by date (column D) in order to compare to our demand. Ultimately, I am looking to compare total demand by date to total inbound by date.

The results should go on the "Automated Requirements" wksht in the highlighted area. The multiple results can go horizontally across the various container locations (1-10).

https://www.dropbox.com/s/lsnxr8m0q5raoie/Build Plan POC_PUB.xlsx?dl=0

Thanks for your help!!

 

[mick0005]

Hi everyone, I managed to figure out a portion of my problem. Answer for that is at the end of this post. However, now I have a new challenge related to this, and I guess I still consider it a 2D lookup with multiple results, but its not the same kind of 2D lookup I am used to seeing done.

In my image, I have put a border around the ranges I need to evaluate for criteria and I have highlighted in yellow the range that will get the results from. Basically I need to evaluate a vertical list of Items, find the corresponding columns where that item has a value >0 (or not blank), evaluate the date for that column against a date cell I will use as criteria, and then return the container number that that item has a quantity for. Brownie points would be awarded if you could also result in the corresponding qty on the shipment for that container (in this example, 132 and 2880 are the shipment volumes for various items).

The formula I used to get me the TOTAL shipping qty by Item and Date was this array formula

=SUMPRODUCT(('Shipping Schedule'!$A$9:$A$69='PN (1)'!$B$1)*('Shipping Schedule'!$N$6:$AX$6='PN (1)'!$A11)*'Shipping Schedule'!$N$9:$AX$69)

 

[mick0005]

Bump