Return to Excel Formulas List

XLOOKUP by Date – Excel & Google Sheets

Download Example Workbook

Download the example workbook

This tutorial will demonstrate how to use the XLOOKUP Function with dates in Excel.

XLOOKUP by Date Main

 

In Excel, dates are stored as serial numbers where each number represents a unique date (the number of days from the imaginary day 1/0/1900).
DATE Serial number

Later in this tutorial we will discuss how to deal with dates stored as text, but for now, we will assume all dates are stored as serial numbers.

XLOOKUP with Date Function

One way to enter a date into an XLOOKUP formula is to use the DATE Function.

XLOOKUP by Date 01

The DATE Function generates a date from a given year, month, and day.

XLOOKUP with DATEVALUE Function

Another method to create a date is to use the DATEVALUE Function.

XLOOKUP by Date 02

The DATEVALUE Function converts a string of text that represents a date, into a date that Excel can work with (a serial number date).

XLOOKUP with Dates stored in Cells

Alternatively, you can reference a cell containing a date to perform an XLOOKUP.

XLOOKUP by Date 03

 

XLOOKUP with Multiple Dates

We can reference a list of lookup dates, which will convert the formula to a dynamic array formula.

XLOOKUP by Date 04

Note: When a list of values is inputted in an argument that normally can only accept a single value, a dynamic array or Spill Formula is created. The formula will “spill” to perform the calculations on the entire array of values.

XLOOKUP with Approximate Match

By default, the XLOOKUP will perform an exact match. Changing match_mode (5th argument) to -1 or 1 will search for an inexact match instead.

XLOOKUP with Next Smaller Item

We can search for the exact date or the latest date before the lookup date by changing the match_mode to -1.

XLOOKUP by Date 05

Note: If the XLOOKUP can’t find the exact match, it will return the largest value that is less than the lookup value.

XLOOKUP with Next Larger Item

If we want to return an approximate value that is greater than the lookup value, then we need to apply match_mode = 1.

XLOOKUP by Date 06

Note: If the XLOOKUP Function can’t find the exact match, it will find the smallest value that is greater than the lookup value.

XLOOKUP with Last Date

By default, the XLOOKUP Function will start searching top to bottom. Changing search_mode (6th or last argument) to -1 will change the search order to Bottom-up.

We can search for the last occurrence of the lookup date within the list by inputting -1 in the last argument.

XLOOKUP by Date 07

 

XLOOKUP with Date-Time Data Type

As noted above, Excel stores dates as whole serial numbers. Time is stored as a decimal value representing the fraction of the day.

To get the serial number only, we can use the INT Function.

XLOOKUP by Date 08

The INT Function returns only the integer (date) portion of a number.

XLOOKUP Date Problems

Usually issues with Date lookups, stem from issues with the data type of the raw data (e.g., text, number, date). One important rule for the XLOOKUP Function is that the data type of the lookup value must be the same with the data type of the values within the lookup list. If the dates in the data set are in text and the lookup value is in date, then the XLOOKUP Function will return the #N/A error.

XLOOKUP by Date 09

Dates as Texts

The fastest way to check if the dates are in text is to look at the alignment of the data. If it’s left aligned, then it’s a text, and if it’s right aligned, then it’s a number.

Another way is to look at the number formatting. Just go to Home > Number > Number Format and check if the formatting is Text.

To convert the number stored as text to a number, you can use the DATEVALUE Function or another one of the options discussed in the linked tutorial.

Imported or Copied Dates

Data that are imported or copied from other sources (e.g., web, csv) will not be in date data type most of the time. To fix this problem, we can use the Text to Columns Tool of Excel to fix our date problem.

Steps:

  1. Highlight the dates and go to Data > Data Tools > Text to Columns

XLOOKUP by Date 10

  1. In the popup window, select Delimited and click Next.

XLOOKUP by Date 11

  1. In the next step, select Tab and click Next.

XLOOKUP by Date 12

  1. In the last step, select Date and the format and click Finish.

XLOOKUP by Date 13

  1. The date will be converted into a date data type.

XLOOKUP by Date 14