Find the next date before a date with a matched ID number Excel -
i have list of data 200k plus lines. need search next order date before date, matches id number in excel. know can use index match find next date before given date, how when need id numbers match given id? have attached sample format of data searching in. problem not search range of dates, need next date before date. there multiple dates before given date, need pull 1 before.
index match formula find next given date.
=index(orders!b:b, match(min(abs(orders!b:b-f3)), abs(orders!b:b-f3), 0))
id date 1 7/22/2015 2 4/27/2016 3 7/6/2016 2 4/23/2016
another way state requirement find maximal date in b:b
< f3
, has in a:a
id
specified in e3
. following formula does:
=aggregate(14,6,orders!b2:b999/(orders!b2:b999<f3)/(orders!a2:a999=e3),1)
aggregate(14, ...., 1)
max result in given arraythe divisions criteria generate
div!0
in array entries don't match criteriaparameter
6
instructs function ignore error entries, including divisions0
notice although formula not require cse, array-based, avoid using full-columns because slow-it down. choose reasonable number of rows (i.e. a2:a999
) sufficient span data.
Comments
Post a Comment