VLOOKUP Dates Not Working? Find and Fix the Cause

VLOOKUP dates usually fail because Excel is comparing a real date value with text, a date-time value, or a different date than the one displayed. Set VLOOKUP to exact match with FALSE, then check that the lookup date and the first column of the lookup range contain matching date values; formatting alone does not make different underlying values match.

How VLOOKUP matches dates

Excel stores most dates as serial numbers: whole numbers that count days from a starting date. A cell displayed as 3/5/2024 may therefore contain the number 45356, while another cell that looks identical may contain the text characters “3/5/2024.” The cells can look the same on screen but are different values, so an exact-match lookup may not find a match.

VLOOKUP searches only the leftmost column of its table range, then returns a value from a column to the right. The final argument controls whether it seeks an exact match. For dates, explicitly use FALSE (or 0) unless you have a specific reason to use an approximate match. If omitted, the argument defaults to approximate matching, which can return a misleading result when the first column is not sorted as required.

Assume the lookup date is in E2, dates are in A2:A100, and the corresponding results are in B2:B100. This exact-match formula returns the result from column B when E2 matches a date in column A; for example, it returns 125 if the matching row’s B cell contains 125:

=VLOOKUP(E2,$A$2:$B$100,2,FALSE)

A date’s display format—such as 5-Mar-24 versus 3/5/2024—does not affect a match if the underlying values are equal. Conversely, applying a date format to text does not convert that text into a real date.

Start with these quick checks

Before changing the workbook, check the most common causes in this order. This usually narrows the issue down faster than rebuilding the formula.

Confirm the exact-match argument. Make sure the formula ends in FALSE or 0, rather than omitting the last argument or using TRUE.
Check the lookup range. The dates must be in the first column of the selected table array, and the column index must point to the intended return column.
Compare the underlying values. Look for text dates, times attached to dates, or a lookup date that is genuinely different from the source date.
Check the displayed year and regional interpretation. A value entered as 4/5/2024 can mean April 5 or May 4 depending on the input convention.
Look for errors in the result. #N/A commonly means no exact match was found; #REF! often points to an invalid return-column index.

For a quick visual check, change the number format of both date cells to General. True dates usually display as serial numbers, while text dates remain text. This is a clue rather than a complete test: a cell can contain a formula, and an apparently numeric value may still need closer inspection.

Convert text dates into real Excel dates

Imported CSV files, copied reports, and systems that use a different regional date convention often bring dates into Excel as text. Changing the cell format to Short Date will not convert them. One reliable option on Excel for Microsoft 365 on Windows is Text to Columns: select the affected cells, choose Data > Text to Columns, select Delimited, choose Next twice, set the column data format to Date, choose the order that matches the source (such as MDY or DMY), and select Finish. Verify a few converted dates before using them in a lookup.

For a formula-based conversion, first identify which cells contain text and confirm how their dates are written. DATEVALUE interprets text according to Excel’s regional settings, so it is not a safe blind conversion when the source format is ambiguous. For example, 03/04/2024 could be March 4 or April 3. If you know the text represents a specific month, day, and year, constructing the date with DATE avoids that ambiguity.

Assume A2 contains the year 2024, B2 the month number 3, and C2 the day number 5. This formula creates the real date March 5, 2024; format the result as a date if you want to see a calendar-style display:

=DATE(A2,B2,C2)

If E2 specifically contains a text date that Excel can interpret under the computer’s regional settings, while A2:A100 contains genuine dates, DATEVALUE can convert the lookup value for the comparison. In a sample where the matching row’s return value is 125, the formula returns 125:

=VLOOKUP(DATEVALUE(E2),$A$2:$B$100,2,FALSE)

This approach does not fix text dates in the first column of the lookup table. Convert that column too, or create a separate normalized date column and search it. On Mac, the ribbon may be arranged differently, but use the Data tab’s Text to Columns command where available; if the command or dialog differs in your build, use a helper column with a conversion formula and verify the results.

Check for hidden time values

A date-time value includes a fractional part for the time of day. For example, midnight and 2:30 p.m. on the same calendar day have different stored values, even though a cell formatted to show only the date may display both as 3/5/2024. An exact VLOOKUP will not treat them as equal. This often happens when data comes from a database, web export, or timestamped transaction list.

To inspect a suspected value, temporarily display more decimal places or apply a format that includes the time. If the lookup should match by calendar day, remove the time component from both sides in a helper column. With source date-times in A2:A100, put this formula in a new helper column beside the source data, then fill it down. It returns the date portion at midnight; format the helper values as dates:

=INT(A2)

With the normalized dates in C2:C100, the return values in B2:B100, and the lookup date-time in E2, this formula searches the normalized date column and returns the corresponding B value. In the example, if the first matching row’s B value is 125, the result is 125:

=VLOOKUP(INT(E2),$C$2:$B$100,0,FALSE)

That range is not valid for VLOOKUP because its return column must be to the right of the first column. Arrange the helper and return columns with the helper first—for example, put normalized dates in C and return values in D—and use this formula instead. For the matching row described, it returns 125:

=VLOOKUP(INT(E2),$C$2:$D$100,2,FALSE)

Do not remove times if the time is meaningful to the record. Two transactions on the same day may need to remain distinct; in that case, match the complete date-time value or use another unique identifier.

Fix the range and column index

A correct date can still appear not to work when the table array is misaligned. VLOOKUP searches the leftmost column of the range you give it, not necessarily column A on the worksheet. If the dates are in B and the return values are in A, a range beginning at A cannot look left to retrieve A after finding a date in B. Reorder the data, create a helper table, or use a lookup method that can return from either side.

The column index counts from the first column of the selected range. In $A$2:$C$100, column A is 1, B is 2, and C is 3. A mistaken index may return the wrong field, while an index larger than the number of columns in the range produces #REF!. Lock the range with dollar signs before copying a formula down so that it does not slide to different rows.

Also check for duplicate dates. VLOOKUP returns the first matching row it encounters, not every matching row. If one date appears on multiple records, that behavior may look like a wrong result even though the match is working as designed. Use a more specific key, such as a date plus an account or item ID, if the date alone does not identify one record.

Understand #N/A and approximate-match mistakes

#N/A means VLOOKUP did not find the requested value under the chosen match rules. With dates, common reasons include text versus numeric values, an unwanted time fraction, an incorrect year, or searching the wrong column. It can also mean the lookup date truly is absent. Compare a known source cell and the lookup cell rather than assuming that the displayed dates tell the whole story.

Approximate match is a separate source of confusing results. When the final argument is TRUE or omitted, VLOOKUP expects the first column to be sorted in ascending order. It can return the nearest lower value rather than an exact date. For example, a lookup for a date between two listed dates may return the earlier date’s row. That behavior can be useful for date bands or effective-date tables, but it is not appropriate for ordinary exact date matching unless the data is deliberately structured and sorted for that purpose.

Do not hide #N/A with IFERROR until you have decided what the error means. Returning a blank can make a broken lookup appear successful and conceal new dates that have not been added to the table. A better first step is to resolve the mismatch or make a deliberate display choice once the underlying cause is understood.

When to use a different lookup method

VLOOKUP remains useful when the lookup key is in the first column and the return value is to its right. If your data layout does not fit that shape, XLOOKUP can search one range and return from another without a column index. XLOOKUP is available in Microsoft 365 and Excel 2021 or later; it is not available in some older perpetual Excel versions. In Excel for the web, availability can depend on the workbook’s supported function set, but current Microsoft 365 web Excel includes it.

Assume dates are in B2:B100, return values are in A2:A100, and the lookup date is E2. This XLOOKUP searches B and returns the corresponding value from A, with an explicit message if there is no match. If the date is found on a row where A contains 125, it returns 125:

=XLOOKUP(E2,$B$2:$B$100,$A$2:$A$100,"Date not found",0)

In XLOOKUP, the final 0 requests exact match; exact matching is also the default, but stating it makes the intention clear. If you need to keep compatibility with older Excel files or users, VLOOKUP with an exact-match argument remains a practical option. Whatever function you use, the source and lookup values must still represent the same date and, where relevant, the same time.

Frequently Asked Questions

Why does VLOOKUP show #N/A when both dates look the same?
They may not be the same underlying value. One could be text and the other a serial date, or one could include a hidden time. Check the stored values, convert text dates, and compare date-times before changing the display format.

Does changing the cell format fix a date lookup?
No. Number formatting changes how a value appears, not whether text is converted into a date or whether a time fraction is removed. Convert text to real dates and normalize time values when the lookup should match by day.

Should I use TRUE or FALSE for VLOOKUP with dates?
Use FALSE for an exact date match in ordinary lists. TRUE, or an omitted final argument, uses approximate matching and requires the first lookup column to be sorted in ascending order.

Can VLOOKUP match a date that also has a time?
Yes, but an exact match requires the date and time values to match. If the time should not matter, normalize both lookup and source values to the date portion, such as with an INT helper column.

Why does VLOOKUP return the wrong row for a date?
Check whether approximate matching is enabled, whether the dates are sorted as required, and whether the date occurs more than once. With exact matching and duplicates, VLOOKUP returns the first matching row in the range.

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

More in Errors & fixes

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

Spotted an error in this guide? Let us know.