Excel XLOOKUP return value for TODAY

Hi,

Try this

=XLOOKUP(TODAY(), 'Daily status and % graphs (2)'!EK311:LS311, 'Daily status and % graphs (2)'!EK313:LS313,,-1)

Just added a -1 in the 5th argument of the function.

Hope this helps.

The following formula expands the one you provided to perform the additional testing you requested.

=LET(a,EK311:LS313,m,XMATCH(TODAY(),CHOOSEROWS(a,1)),v,INDEX(a,3,m),IF(v>0,v,INDEX(a,3,m-1)))

a is just a convenient shorthand for the array of cells your data occupies.

XMATCH sets m to the relative column number in the array that hold's today's data.

INDEX sets v to the percentage value for today.

IF selects today's value when positive or the previous value (one column to the left) otherwise

Dear @Office worker,

As a moderator, I do not have the tools to investigate your specific account directly because this is a user-to-user support forum. Our role is limited to providing technical support on reported issues, requests, or ideas. However, I will try my best to clarify as clearly as possible. To ensure I understand your request correctly and to support you as effectively as possible:

• Could you please provide me the another tab Excel in private message?

The information you provide will allow me to assist you quickly and efficiently.

Use XLOOKUP’s match and search modes so the formula returns the latest available (non‑blank) value up to today’s date instead of only today’s column.

Assuming the date row 'Daily status and % graphs (2)'!EK311:LS311 is sorted left‑to‑right in ascending order and the percentages are in 'Daily status and % graphs (2)'!EK313:LS313, use:

=XLOOKUP(
    TODAY(),
    'Daily status and % graphs (2)'!EK311:LS311,
    'Daily status and % graphs (2)'!EK313:LS313,
    ,               /* if_not_found (optional) */
    -1,             /* match_mode: exact or next smaller date */
    1               /* search_mode: left-to-right */
)

Explanation of key arguments (based on XLOOKUP syntax):

• match_mode = -1 tells XLOOKUP to find an exact match; if none exists (for example, today’s date has no value yet), it returns the next smaller item, i.e., the previous date’s percentage.
• search_mode = 1 keeps the normal left‑to‑right search.

This way, when today’s percentage is missing, the dashboard shows yesterday’s percentage instead of 0%.

References:

• XLOOKUP function