How to Look Up a Value by Date in Excel

A lookup by date in Excel works just like a lookup by ID or name, because Excel stores every date as a number. You can find an exact date with XLOOKUP or VLOOKUP, or find the value that was in effect on a date, such as the price on the day of an order. This guide shows both, and fixes the two problems that break most date lookups: text dates and hidden times.

How Excel stores dates#

Every date in Excel is a whole number: the count of days since 1 January 1900. So 15 March 2024 is stored as 45366, and the cell's format just shows it as a date. That is why you can subtract dates, sort them and look them up like numbers.

Two things follow from this:

• A date lookup only works if both sides are real dates (numbers). Text that looks like a date is not a number.
• A date with a time is a number with a decimal. 15 March 2024 at 6pm is 45366.75, which is not equal to 45366.

The date basics lesson explains date serial numbers in more detail.

Find an exact date with XLOOKUP or VLOOKUP#

To find the value for one specific date, use an exact match, as with any other lookup. Say a daily sales log has dates in A2:A8 and sales in B2:B8, with the date to find in D2:

=XLOOKUP(D2,A2:A8,B2:B8,"No sales logged")

If the date is there, you get that day's sales. If not, you get the message. In older versions, use VLOOKUP with FALSE:

=VLOOKUP(D2,A2:B8,2,FALSE)

To put a fixed date inside the formula, use the DATE function rather than text: =XLOOKUP(DATE(2024,3,15),A2:A8,B2:B8). DATE takes the year, month and day in that order, so it works the same in every region. A typed text date like "15/03/2024" depends on your computer's regional settings.

Find the value in effect on a date#

Often the date you have isn't in the table, because the table lists changes. A price list, an exchange rate table or a salary history only records the day each value started. You want the most recent change on or before your date.

An order on 20 July 2024 should use the price that started on 15 June: 4.90. With the order date in D2:

=XLOOKUP(D2,A2:A6,B2:B6,,-1)

Match mode -1 means "exact match, or the next smaller value". 20 July isn't in the list, so XLOOKUP takes the latest earlier date, 15 June, and returns 4.90. An order on exactly 1 September returns 5.10, because an exact match wins.

The VLOOKUP version uses TRUE for an approximate match:

=VLOOKUP(D2,A2:B6,2,TRUE)

It gives the same 4.90, but only if the dates are sorted oldest to newest. XLOOKUP doesn't need sorting. This is exactly the same method as a bracket lookup, with dates instead of income limits; see tax bracket and tier lookups for the full pattern.

What happens before the first date#

If the order date is earlier than every date in the table, there is no "next smaller" date, so both formulas return #N/A. An order on 10 December 2023 has no price in this list.

Add a message as XLOOKUP's fourth argument to make this clear:

=XLOOKUP(D2,A2:A6,B2:B6,"Before price list",-1)

Or add an earlier first row to the table if the first price applied before that date too.

Why a date lookup returns #N/A#

When a date you can see in the table returns #N/A, one side is not what it looks like. Check these:

Times sneak in from timestamps and system exports. For an exact-date lookup on timestamped data, use match mode -1 against a list of dates, or strip the times with INT first.

Look up by month, or by date and another value#

To match by month instead of a full date, compare the first day of each month. Put the first day of the month in a helper column with =EOMONTH(A2,-1)+1, then look up EOMONTH(D2,-1)+1 in that column with an exact match.

If the lookup also depends on something else, such as a product and a date, you need two conditions. The guide to lookups with multiple criteria shows how. To find the most recent entry for a name, read how to look up the last match. If the value you need sits to the left of the date column, see lookups to the left. And to add up values between two dates rather than find one, use SUMIFS with a date range.

For full syntax, see the XLOOKUP function guide and the VLOOKUP function guide.

Practise it#

Date lookups combine two skills: match modes and real dates. Practise XLOOKUP match modes in a live spreadsheet: you type the formula and it checks the result instantly. Then practise date basics so you can spot a text date or a hidden time before it breaks your lookup.