Excel Return Date before another date by another column name

Question

I have two sheets of data with the following.

I want return the date from sheet 2 on sheet 1 with the start date BEFORE the remove date. For example. For Customer A, the remove date is 12/29/2016. I would want to return the date of 12/20/2014 from sheet 2 as it is the next previous date from the remove date of 12/29/2016 from sheet 1 and is removed prior to the third date of 1/20/2017. Similarly for Customer B, the date returned would be 11/9/2013.

Vlookup will not work as there are more than one unique value for customer and I had no luck with index and match. Is there any other possible solution?

Here is an edit. The Sumproduct and aggregate method for the start date works great. The issue is I need the last date to be the first record after the replacement date. So in this case, it would be 2/1/16. Using the Min or Max sumproduct and aggregate method, I get either 0 or the max date of 3/22/17, which is not what I want.

Answer 1

Since dates are (for all intents and purposes) numbers, this is really nothing more than a pseudo-MAXIFS question. Try,

=aggregate(14, 6, (b2:b7)/((a2:a7="A")*(b2:B7<date(2016, 12, 29))), 1)

Answer 2

For those that don't have the latest version of excel and can't use maxifs, we can do an array formula: take the max date that is less than the date corresponding to A

=SUMPRODUCT(MAX((A9:A14=A3)*(IF((B9:B14)<B3,B9:B14))))

entered as array formulae (Ctrl+Shift+Enter)