Pull data from other tab

You're trying to pull the current PTO balance based on today's date, not the last entry of the year.

Here’s how to do it, assuming your data layout:

Example layout on your PTO tab

Let’s say:

• Column A = Date (each row is a PTO accrual date or month-end balance date)
• Column B = PTO Available Hours

Example:

Goal on Calendar tab

Pull the PTO available for the most recent date on or before today.

Solution formula (on Calendar tab)

=XLOOKUP(TODAY(), 'PTO tab'!A:A, 'PTO tab'!B:B, , -1)

How it works:

• TODAY() = today’s date
• 'PTO tab'!A:A = date column on PTO tab
• 'PTO tab'!B:B = PTO hours column
• -1 = next smaller item (exact match or next smaller date if exact not found)

This returns the PTO balance for the latest date ≤ today.

If your dates are not sorted ascending

Use this instead (works even if unsorted):

=LOOKUP(2, 1/('PTO tab'!A:A <= TODAY()), 'PTO tab'!B:B)

Or with MAXIFS (if you have Office 365 or Excel 2019+):

=MAXIFS('PTO tab'!B:B, 'PTO tab'!A:A, "<="&TODAY())

But MAXIFS gives the max balance on/before today, not necessarily the latest date's balance — use only if PTO always increases.

If PTO resets yearly and you have multiple years

Add a year check:

=XLOOKUP(1, ('PTO tab'!A:A <= TODAY()) * (YEAR('PTO tab'!A:A) = YEAR(TODAY())), 'PTO tab'!B:B, , -1)

Hope that helps. Explaining with help from AI🙂