CODEWITHSUNDEEP
exceldate
ANN
ANN

Reputation: 73

Find the next date before a date with a matched ID number Excel

I have a list of data with 200k plus lines. I need to search for the next order date before a certain date, that matches an ID number in excel. I know I can use index match to find the next date before a given date, but how would I do that when I need the ID numbers to match a given ID? I have attached a sample format of my data I am searching in. The problem is also that I am not search by a range of dates, I need the next date before a certain date. There are multiple dates before a given date, I need to just pull the one before.

Index match formula to find the 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

Upvotes: 1

Views: 431

Answers (1)

A.S.H
A.S.H

Reputation: 29352

Another way to state your requirement is to find the maximal date in B:B that is < F3 and has in A:A the ID specified in E3. This is exactly what the following formula does:

=AGGREGATE(14,6,Orders!B2:B999/(Orders!B2:B999<F3)/(Orders!A2:A999=E3),1)

  • AGGREGATE(14, ...., 1) get the max result in the given array

  • The divisions by the criteria will generate DIV!0 in the array entries that don't match the criteria

  • Parameter 6 instructs the function to ignore the error entries, including those divisions by 0

Notice that although this formula does not require CSE, it is array-based, so avoid using full-columns because they slow-it down. Choose a reasonable number of rows (i.e. A2:A999) that is sufficient to span your data.

Upvotes: 2

Related Questions