Does VLOOKUP Work with Dates in Excel?

Formulas & functions · Updated November 20, 2026

Yes. VLOOKUP works with dates in Excel when the lookup date and the dates in the first column of the lookup range are genuine Excel date values that match; use FALSE for an exact match. Dates that are stored as text, include hidden time values, or differ from the lookup date can cause a seemingly correct formula to return #N/A.

How Excel stores dates

Excel does not store a date as a special text label. It stores a date as a number, called a serial value, and applies a date format to display that number in a familiar way. For example, the same underlying date might display as 8-Mar-2025, March 8, 2025, or 3/8/2025, depending on the cell format and regional settings. Changing the display format does not change the stored date.

This matters because VLOOKUP compares underlying values, not just what the cells look like. Two cells can display the same date while one contains an actual date and the other contains text characters. A date typed into a cell and recognized by Excel is usually stored as a number; a date imported from a website or text file may remain text. Formatting text to look like a date does not convert it into a real date value.

Excel workbooks can also use the 1900 or 1904 date system. The difference is most relevant when copying raw date values between workbooks configured with different systems; displayed dates can shift if values are transferred incorrectly. For ordinary lookups within one workbook, the key practical check is that both sides contain compatible date values.

Use an exact-match VLOOKUP for dates

The usual date lookup should use VLOOKUP’s fourth argument as FALSE (or 0), which requests an exact match. The date being searched for must be in the leftmost column of the table array, and the column number tells Excel which column in that array contains the result. Exact-match VLOOKUP does not require the date column to be sorted.

For example, assume A2:A4 contain actual dates and B2:B4 contain corresponding amounts: March 1, 2025 has 120, March 8 has 145, and March 15 has 160. With that layout, the following formula searches for March 8 and returns 145:

With dates in A2:A4 and amounts in B2:B4, this formula returns 145: =VLOOKUP(DATE(2025,3,8),$A$2:$B$4,2,FALSE)

DATE constructs a date value from year, month, and day numbers, avoiding ambiguity about whether a typed date such as 3/8/2025 means March 8 or August 3. In the formula, the lookup range begins in column A, and 2 means return the value from the second column of that range, column B. The dollar signs keep the range fixed if you copy the formula to other cells.

You can also put the lookup date in a worksheet cell and refer to it. With a genuine date in D2, dates in A2:A4, and amounts in B2:B4, this formula returns 145 when D2 is March 8, 2025: =VLOOKUP(D2,$A$2:$B$4,2,FALSE)

Format D2 as a date so it is easy to read, but remember that formatting is only presentation. If your Excel regional settings use semicolons as formula separators, Excel may require semicolons instead of the commas shown in these examples.

Check whether dates are real dates or text

A frequent cause of #N/A is a mismatch between a real date on one side and text that looks like a date on the other. This commonly happens after importing CSV files, copying data from a web page, or combining information from systems with different date conventions. For example, text written as 04/05/2025 is ambiguous across regions, while an actual date value is unambiguous once Excel has interpreted it.

Try selecting a date cell and changing its number format to General. A genuine date will display as a number; a text date will generally remain visible as text. You can also test a cell with ISTEXT or ISNUMBER, though a numeric cell is not necessarily a date—it could be any number. The worksheet’s source data and intended date range still matter.

If the text is consistently recognized by your regional settings, DATEVALUE can convert it. With a date string in A2, the formula below returns an Excel date value for March 8, 2025 when A2 contains text Excel recognizes as that date; format the result cell as a date to display it clearly:

With the text date in A2, this formula returns the date value for March 8, 2025: =DATEVALUE(A2)

DATEVALUE depends on how the text is written and interpreted on that computer, so it is not a reliable fix for every mixed-format import. For data with a known year, month, and day stored in separate columns, DATE can construct a dependable date from those parts. For a small number of cells, re-entering the date in a recognized format may be simplest. After conversion, use the converted date column as the lookup key rather than leaving one side as text.

Watch for dates that include times

Excel stores times as fractions of a day. As a result, a cell containing March 8, 2025 at 9:30 a.m. has a different underlying value from midnight at the start of March 8, even if both cells are formatted to display only the date. An exact VLOOKUP for March 8 will not match the value that includes 9:30 a.m.

To check for this problem, temporarily display the lookup and source cells with a format that shows both date and time, or inspect the formula bar. If the time is not important to the lookup, a helper column can remove it. With a date-time value in A2, this formula returns the date portion; fill it down for the other rows and format the helper cells as dates:

With a date-time in A2, this formula returns its date portion, such as March 8, 2025: =INT(A2)

For a lookup using the helper column, put the cleaned dates in C2:C4 and the corresponding results in D2:D4. With those columns in place, the formula below returns the result for March 8, 2025, provided that date appears in C2:C4:

With date-only keys in C2:C4 and results in D2:D4, this formula returns the matching result: =VLOOKUP(DATE(2025,3,8),$C$2:$D$4,2,FALSE)

Use this approach only if the time should be ignored. If the time distinguishes separate records, removing it would make different entries share the same lookup key.

When approximate matching is appropriate

VLOOKUP can also perform approximate matching when its fourth argument is TRUE or omitted. For dates, this finds an exact date if present; otherwise, it returns the row for the greatest date that is less than the lookup date. The first column of the lookup range must be sorted in ascending order for this behavior to be reliable. If it is not sorted, the result can be unexpected.

Approximate matching can be useful for date bands, such as applying a rate that changes on specified effective dates. Suppose A2:A4 contain March 1, March 8, and March 15, 2025 in ascending order, with amounts 120, 145, and 160 in B2:B4. With this layout, a lookup for March 10 returns 145, the value attached to the latest date not after March 10:

With ascending dates in A2:A4 and corresponding values in B2:B4, this formula returns 145 for March 10, 2025: =VLOOKUP(DATE(2025,3,10),$A$2:$B$4,2,TRUE)

Do not use approximate matching just to make a failed exact lookup produce something. It may return a nearby earlier date and hide a text-date or time problem. For a missing exact date, FALSE produces #N/A, which makes the missing match visible; approximate matching changes the question Excel answers.

Common errors and ways to diagnose them

If a date lookup fails, check the data and formula in a deliberate order rather than changing the match setting at random. These checks catch most everyday problems:

  • Confirm that the dates are in the first column of the table array. VLOOKUP cannot return a value from a column to the left of that column.
  • Check that the fourth argument is FALSE for an exact lookup, and that the column index points to the intended result column.
  • Compare the lookup cell with the source dates using General format or a date-and-time format to find text values or hidden times.
  • Look for extra spaces or inconsistent imported data when a date is text; a date format alone will not remove them or convert them.
  • Check that the lookup range includes the row containing the date and that copied formulas have not shifted the range.
  • Consider duplicate dates. Exact-match VLOOKUP returns the first matching row from top to bottom, so it will not automatically choose the newest or last record.

For a missing exact match, #N/A is often useful evidence rather than a formula defect: it says no equal lookup value was found in the first column of the specified range. If you wrap the formula in an error-handling function, make sure the message does not conceal a data-quality issue that needs fixing.

VLOOKUP alternatives for date searches

VLOOKUP is suitable when the date key is at the left edge of a table and the return value is to its right. If your layout puts the return column to the left, you can rearrange the table or use another lookup method. XLOOKUP is available in Microsoft 365 and Excel 2021 or later; it can search one range and return a result from a separate range, and exact match is its default. INDEX with MATCH is another option in older Excel versions and can also look in either direction.

These alternatives do not eliminate date mismatches. XLOOKUP and INDEX/MATCH still need compatible underlying values, and a hidden time can still prevent an exact date match. Choose a different function for layout flexibility or clearer formulas, not as a substitute for checking whether the dates are real dates and whether they contain times.

Frequently Asked Questions

Can VLOOKUP return a date?
Yes. VLOOKUP can return a date value from the selected result column. If the result appears as a number, apply a date format to the result cell; the underlying returned value may already be a valid date.

Why does VLOOKUP return #N/A for a date that looks the same?
The lookup value and source value may differ underneath: one could be text, or one could include a time. They may also be different dates displayed with similar formats. Check the values and use an exact match when appropriate.

Does the date column need to be sorted?
No, not for an exact match using FALSE. Sorting in ascending order is required for reliable approximate matching with TRUE.

Can VLOOKUP find the closest earlier date?
Yes. With an ascending date column and TRUE as the fourth argument, VLOOKUP returns the row for the greatest date less than or equal to the lookup date. This is useful for effective-date bands, but not for confirming an exact match.

Can VLOOKUP search dates that include times?
Yes, if the lookup value has the same date-and-time value as the source. A date-only lookup will not exactly match a date-time value with a nonzero time fraction; use a helper column to remove times only when time is irrelevant.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.