How to use VLOOKUP in Excel by Date

Understanding Date Storage and VLOOKUP Compatibility

Before diving into the formulas, it is essential to recognize how Excel internally manages date entries. Unlike general text or numerical data, dates in Excel are stored as sequential numbers, known as serial date numbers. This numbering system begins with January 1, 1900, which is represented as the number 1. For example, the date January 1, 2024, is stored as 45305 because it is the 45,305th day since the base date.

The core challenge when performing a date lookup is ensuring that the lookup value (the date you are searching for) matches the format used in the table array. If you attempt to look up a date entered as a text string (e.g., “1/14/2023”) against a column formatted as numerical dates (i.e., serial date numbers), the VLOOKUP function will invariably fail, returning an error like #N/A. Therefore, the goal is always to provide VLOOKUP with a valid serial date number for the lookup argument.

Two Valid Methods for VLOOKUP by Date

There are two robust and reliable methodologies for implementing a precise date lookup using VLOOKUP in Excel. The choice between them depends primarily on whether your lookup criterion is stored in a separate cell or provided directly as a text string within the formula itself.

These methods ensure that the lookup value is correctly interpreted as a numerical date by VLOOKUP, allowing for successful matching against the dates stored in the table array.

Method 1: Utilizing VLOOKUP with a Cell Reference

The simplest and most recommended approach is to reference a cell that already contains a properly formatted Excel date. When a date is entered into a cell and recognized as such by the program, it is automatically stored internally as its corresponding serial date number. Referencing this cell allows the VLOOKUP function to access the numeric value directly, bypassing conversion issues.

This method is preferred for dynamic dashboards or reporting where the lookup date may change frequently, as you only need to update the date in the referenced cell, not the formula itself. This keeps the formula structure clean and highly adaptable for various lookup dates.

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

In the syntax above, the formula searches for the date contained in cell D2 within the specified range A2:B9 and subsequently returns the corresponding value located in the second column (column 2) of that range. The final argument, FALSE, ensures we locate an exact match for the date.

Method 2: Employing VLOOKUP with the DATEVALUE Function

The second method becomes necessary when the lookup date must be hardcoded directly into the formula, typically as a text string enclosed in quotation marks (e.g., “1/14/2023”). As established, VLOOKUP cannot match a text string against a numerical date format. To solve this, we utilize the DATEVALUE function.

The DATEVALUE function is specifically designed to convert a date represented as text into its numerical equivalent—the serial date number. This conversion allows VLOOKUP to perform the intended matching operation successfully by supplying a numerical lookup key.

=VLOOKUP(DATEVALUE("1/14/2023"), A2:B9, 2, FALSE)

In this formula structure, the DATEVALUE part first converts the string “1/14/2023” into its underlying number. This number is then used by VLOOKUP to find the exact match within the data range A2:B9, returning the value from column 2.

Crucial Note on Exact Matching: Regardless of which method is chosen, the final argument in the VLOOKUP function, known as range_lookup, must be set to FALSE. This setting mandates that Excel search only for an identical date match. Setting this to TRUE (approximate match) is usually only appropriate if your dates are sorted and you are looking for the next closest date, which is rarely the intent in standard reporting.

Data Setup: Preparing Your Dataset

To illustrate these techniques in practice, we will utilize a sample dataset tracking sales figures across various dates. Proper preparation of this data is the first step toward a successful lookup. Ensure your lookup table is structured correctly with the lookup values (dates) in the leftmost column.

The following dataset, spanning columns A and B, will be used throughout the examples. Column A contains the dates, and Column B contains the corresponding Sales figures we wish to retrieve.

Note that the dates shown in Column A are already recognized by Excel as proper date values, meaning they are stored internally as serial date numbers, which is crucial for successful matching.

Example 1: Dynamic VLOOKUP using Cell Reference

In this first practical scenario, we aim to locate the sales value associated with a specific date (1/14/2023) where the date itself is entered into a separate search cell, which we will designate as D2. This is the most flexible approach for routine data analysis, allowing users to easily change the lookup criteria without altering the underlying formula structure.

We need to instruct VLOOKUP to search for the value in D2 within the table array (A2:B9) and return the corresponding sales figure from the adjacent column (column 2).

The following formula should be entered into cell E2 to execute this dynamic lookup:

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

Observe the result in the screenshot below. By placing the date 1/14/2023 into cell D2, the VLOOKUP function successfully identifies the row matching that date and extracts the associated sales figure from the second column of the lookup array. This confirms that referencing a properly formatted date cell works seamlessly.

As demonstrated, the VLOOKUP formula correctly returns the sales value of 15, which corresponds precisely to the date 1/14/2023 in the source data table.

Example 2: Hardcoded VLOOKUP using DATEVALUE

In the second example, we repeat the search for the sales figure corresponding to 1/14/2023, but this time, the date is provided as a static value within the formula itself. Since the date is entered as a text string (“1/14/2023”), the inclusion of the DATEVALUE function is mandatory for successful execution.

The DATEVALUE function ensures that the date string is converted into a numeric equivalent recognized by the date column in the data table.

We can type the following formula into cell E2 (or any desired output cell):

=VLOOKUP(DATEVALUE("1/14/2023"), A2:B9, 2, FALSE) 

This approach successfully converts the text date, ensuring that the lookup search is conducted against the appropriate serial date number within the table range A2:B9, and returns the result from the second column.

The following screenshot shows how this formula is used in practice, delivering the expected result:

The VLOOKUP formula accurately returns a sales value of 15, confirming that the DATEVALUE conversion was successful.

Troubleshooting: Why Literal Text Dates Fail

It is vital to understand the consequence of failing to convert a text date into a recognized serial date number when hardcoding. If you were to attempt to use the quoted date directly in the VLOOKUP formula without wrapping it in the DATEVALUE function, Excel treats the lookup value as pure text.

Since the dates in the source table (A2:B9) are stored numerically, the text lookup value will never find a match, resulting in the common #N/A error, signifying that the value is “Not Available.” This failure occurs because the numerical value of 1/14/2023 is approximately 44938, but the text string “1/14/2023” has no numerical equivalent that Excel can use for comparison against the date column.

The resulting error is clearly illustrated when omitting the conversion function:

This visual confirmation emphasizes that the DATEVALUE function is mandatory when using date strings directly within the formula. It successfully translates the text representation of the date into the underlying numeric format that VLOOKUP requires for effective comparison with the dates in the lookup column.

Summary of Best Practices for Date Lookups

To ensure robust and error-free date lookups in Excel, keep the following guidelines in mind:

• Consistency is Key: Ensure that the column containing the dates in your lookup table is consistently formatted as an Excel date (i.e., stored as serial date numbers), not as text.
• Prioritize Cell Reference: Whenever possible, use Method 1 (referencing a cell containing a date) as this avoids complex conversions and is generally more flexible for dynamic reporting needs.
• Utilize DATEVALUE for Hardcoding: If you must embed the date within the formula using quotes, always wrap the date string in the DATEVALUE function to convert it to a numerical format.
• Always Use Exact Match: For date lookups, set the range_lookup argument to FALSE to prevent approximate matches that could lead to incorrect results if the exact date is not present.