Excel 2013 - How to have a Lookup return the first date after a specified date?

Question

So I am looking to create a function that searches for a unique ID code in one column, and returns a corresponding time stamp from another column. However, there are multiple time stamps attached to each unique ID code. I want to only return the time stamp that is immediately following a specified time.

For example, for Unique ID "9f45Q3", there are 4 time stamps attached to it:

• 11:00
• 11:15
• 11:40
• 11:55

I need to find a way to tell Excel to return the time stamp for "9f45Q3" that most immediately followed 11:30. ( just an arbitrary time for the sake of explaining) In this case, I would want Excel to return 11:40, because it most immediately follows my specified time.

Any help would be much appreciated. Thank you!

Answer 1

Use INDEX and AGGREGATE

Use aggregate to determine the row number that matches your criteria or the second or the third. In your case second. AGGREGATE performs array like operations so you will want to avoid using full column references.

=AGGREGATE(14,6, row(range of interest)/(true condition check(range of interest)*true condition check n (range of interest)),2)

14 tells AGGREGATE to sort an array of results from smallest to largest.

 6 tells AGGREGATE to ignore any errors and exclude them from the array of results)

ROW(range of interest) will return the row number corresponding to the current calculation

Condition check will be some formula you come up with that returns a TRUE or FALSE result.  If it is false it will result in a divide by 0 calculation which the 6 will tell aggregate to ignore.  You can apply multiple conditions and separate them by a * which will act as an AND function.
 2 tells AGGREGATE to return the 2nd result in the sorted array.  So in this case it should be the second row number that matches your results.

The next thing to do is to place the AGGREGATE function inside of INDEX so it returns the information you want. Since INDEX does not perform array like calculations, it is safe to use full column references. Lets say your time stamp is in column B. You index formula would look something like:

=INDEX(B:B,AGGREGATE())

So in the end assuming your ID was in the A2:A8 range and your time stamp was in the B2:B8 range And the ID code you were looking for was in C1, your formula might look like:

=INDEX(B:B,AGGREGATE(14,6,ROW(A2:A8)/(A2:A8=C1),2)

Now if you want to add a bit of error checking you could includ the IFERROR function and make it look like:

=IFERROR(INDEX(B:B,AGGREGATE(14,6,ROW(A2:A8)/(A2:A8=C1),2),"Could NOT find the darned thing")