CODEWITHSUNDEEP
excelindexingmatchvlookup
Robert Richardson
Robert Richardson

Reputation: 23

Excel 2016 Looking up earliest date that is later than the lookup date, while also exact matching an ID Index(match) or Vlookup

I'm trying to do matching between two spreadsheets using an Id and a Date field. I need an exact match on the ID, but the closest date that is greater than or equal to a date in the other table.

Specifically I'm trying to create the equivalent of the following SQL through a form of index(match) or Vlookup.

 Select min(b.date)
 from a
 join b
   on a.id = b.id
   and b.date >= a.date

If I had access to Access I'd use that, but unfortunately its not an option, so its something I have to do in excel directly.

Any help would be greatly appreciated.

Upvotes: 0

Views: 171

Answers (1)

Scott Craner
Scott Craner

Reputation: 152605

The following two formula will do what you want:

Array MIN(IF()):

=MIN(IF(($B$2:$B$15>=E2)*($A$2:$A$15=E1),$B$2:$B$15))

Being an array formula it needs to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode. If done correctly then Excel will put {} Around the formula.

The Second uses AGGREGATE and gets entered normally:

=AGGREGATE(15,6,$B$2:$B$15/(($A$2:$A$15=E1)*($B$2:$B$15>=E2)),1)

enter image description here

NOTE

Neither formula require the data to be sorted.

Upvotes: 2

Related Questions