Question:

Given a list of pay periods and sales dates. For each sales date, determine the next pay day.

Google Sheets: Yellow = given data, Blue = answer to be generated

Clostest I’ve gotten:

=arrayformula(filter($A$2:$A,abs($A$2:$A-B2)=min(abs($A$2:$A-B2))))

This formula is incorrect as for some of the sales date, the pay day is earlier. The sale date must always come before the pay day because in real life, you’re only paid after you make a sale, not before.

>Solution :

I’m a fan of SQL, so here’s an intuitive solution using QUERY():

=QUERY(A:A, "
    SELECT 
        A 
    WHERE 
        A >= DATE '"&TEXT(B2, "yyyy-mm-dd")&"' 
    ORDER BY 
        A 
    LIMIT 
        1 
    LABEL 
        A ''
    "
    , 1)

I threw it in your Google Sheet so you can see it in action!