CODEWITHSUNDEEP
excelexcel-formulavba
Mark
Mark

Reputation: 69

INDEX and MATCH / VLOOKUP formula to look for nearest future date

I need to have a formula to lookup the Primary Key on the other table and return the value of date. But with a twist, the return value should be the closest future date. Is there a formula for this? Thank you.

Sample Table Here

The return value should be the closest future date in today's date instead of the first item that it will lookup.

Hoping for your kind help. Thanks a lot.

Upvotes: 0

Views: 1462

Answers (2)

Forward Ed
Forward Ed

Reputation: 9874

Use the following:

=AGGREGATE(15,6,$D$6:$D$25/((A6=$C$6:$C$25)*(TODAY()<$D$6:$D$25)),1)

enter image description here

Adjust your ranges as required.

Aggregate performs array like operations. As such avoid using full column references like C:C. Also, Today is a volatile function. As such it will recalculate anytime something on your spreadsheet changes. That combined with the fact that its in an array operation means you will want to limit its usage. If you notice your worksheet bogging down with calculations, either turn automatic calculations off for the sheet, or look at changing TODAY() to a cell reference where a date is entered.

Upvotes: 3

Brownish Monster
Brownish Monster

Reputation: 732

The equation {=MIN(IF(F8:F11>NOW(),F8:F10,402132))}, where F8:F11 is the range of Scheduled Dates will give the minimum date after and closest to the current date.

Note: This is an array formula so you will need to press Ctrl+Shift+Enter.

Then use VLookup or Index/Match as per usual.

Upvotes: 0

Related Questions