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.

user4039065 · Accepted Answer · 2017-03-28 21:55:06Z

2

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)

answered Mar 28, 2017 at 21:55

user4039065

Sign up to request clarification or add additional context in comments.

6 Comments

barry houdini Over a year ago

Re MAXIFS - in the latest version of Excel (if you subscribe to Office 365) that function exists, so you can use something like =MAXIFS(B2:B7,B2:B7,"<"&C1,A2:A7,"A")

user4039065 Over a year ago

It's also in hte Excel 2016 tablet version with or without Office 365.

barry houdini Over a year ago

Ah, I didn't know that - I'm not an Office 365 subscriber but I've used MAXIFS and MINIFS in Office Online, where it's also available

Nich Over a year ago

Can you please see my edit. I am having issue with the last date.

user4039065 Over a year ago

Don't change the conditions of the question after answers have been submitted. You should have chosen one response as the answer and upvoted any of the responses you found helpful. If you have re visions that render these answers inappropriate then you should ask a new question.

|

lostinOracle · Accepted Answer · 2017-03-29 05:12:07Z

1

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)

edited Mar 29, 2017 at 5:12

answered Mar 28, 2017 at 22:04

lostinOracle

4081 gold badge5 silver badges14 bronze badges

6 Comments

Nich Over a year ago

Thanks, this worked. What would I need to change if I wanted to pull the next date and not previous date. So for the first case, it would pull 1/20/17.

lostinOracle Over a year ago

@Nich do the opposite. =SUMPRODUCT(Min((A9:A14=A3)*(IF((B9:B14)>B3,B9:B14))))

Nich Over a year ago

Got it, thanks. I was making the error of not CTRL SHIFT + ENTER and that was my mistake since I wasn't treating it as an array. Thanks again!

lostinOracle Over a year ago

@nich, np glad to help.

Nich Over a year ago

Hi created an edit above. The start date method you told me helped, but the last date does not match with the min method. I need to return the value of 11/10/14 since it falls immediately after the replacement date.

|

Collectives™ on Stack Overflow

Excel Return Date before another date by another column name

2 Answers 2

6 Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related