CODEWITHSUNDEEP
excelexcel-formula
Jeff Brady
Jeff Brady

Reputation: 1498

Excel - Find Previous Date for an Item?

I'm hoping this is something easy .. I'm just not sure how to approach it. I have an item with a date, and need to find the matching item with the previous date.

I have 2 worksheets .. one has an item and a date, and the other sheet has a bunch of items and dates. On Sheet1, I'm trying to put the previous date for the Item in column C:

Sheet1 

A       B           C
Item    Date        Previous Date
ABC     5/1/2016

Sheet2

A       B
Item    Date
ABC     4/22/2016
DEF     11/2/2017
ABC     3/15/2017
ABC     4/28/2016
GHI     2/23/2016

So what I'm looking for here on Sheet2 is "ABC 4/28/2016" as that's the closest date before my date on Sheet1 for that Item.

If it helps, I can sort Sheet2 by date.

Thank you!!

Upvotes: 2

Views: 2927

Answers (1)

Scott Craner
Scott Craner

Reputation: 152660

Use AGGREGATE:

=AGGREGATE(14,6,Sheet2!$B$2:$B$6/((Sheet2!$A$2:$A$6=A2)*(Sheet2!$B$2:$B$6<=B2)),1)

enter image description here

If you do not have AGGREGATE then use MAX as an Array formula:

=MAX(IF((Sheet2!$A$2:$A$6=A2)*(Sheet2!$B$2:$B$6<=B2),Sheet2!$B$2:$B$6))

Being an array formula it needs to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode.

Upvotes: 4

Related Questions