Collecting latest date from another sheet that has multiple matching cells.

I have a master sheet that contains all of the items we have on order. Each individual order typically has multiple items and a separate delivery date. I need a formula to look through the data to return the closest delivery date listed for that item. I have tried INDEX(COLLECT, which I use all the time, I've tried MAX(COLLECT and I keep getting error #INCORRECT ARGUMENT SET messages for both.

=MAX(COLLECT({BB Delivery Date}, {BB PARTS LISTED}, [Part List]@row))

=INDEX(COLLECT({BB Delivery Date}, {BB PARTS LISTED}, [Part List]@row), 1)

Here is a screen shot of the master sheet.

Is the problem here that there are multiple parts with the same name on the same row? Is there a better way or function to return the delivery date with out adding a helper date column next to each column and making the formula really long?

Any help would be greatly appreciated.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!