VLOOKUP works with dates the same way it works with numbers, because Excel stores dates as numbers underneath: =VLOOKUP(D2,A2:B13,2,0) where D2 holds the date you’re looking for. The 0 forces an exact match.
If it returns #N/A even though the date is clearly in the list, one of two things is happening. Either the lookup value or the table dates are stored as text — text dates sit left-aligned in the cell and won’t match a real date, so convert with =VLOOKUP(DATEVALUE(D2),A2:B13,2,0).
Or one of them carries a hidden time component: 15-Jan-2026 09:30 is a different number from 15-Jan-2026, so strip it with =VLOOKUP(INT(D2),A2:B13,2,0). To type a date directly into the formula, wrap it so Excel reads it as a date rather than text: =VLOOKUP(DATE(2026,12,2),A2:B13,2,0).
And if the result comes back as a number like 46023, the return column holds dates and the result cell just needs a date format.
You can also use VLOOKUP to lookup for a date from the date, and then get a value from the same row in the corresponding column. To do this you need to specify a date within the VLOOKUP, or you can also specify it directly into it.
Use VLOOKUP with Dates
You can use the below steps:
- First, enter the VLOOKUP function in a cell.
- After that, in the first argument, refer to cell D2 where you have the date to lookup for.
- Now, in the second argument, specify the range A1:B13.
- Next, in the third argument, enter 2 as you want to get the values from the second column.
- In the end, in the fourth argument, enter 0 to perform the exact argument.
=VLOOKUP(VALUE(D2),A1:B13,2,0)Entering Date Straight into the VLOOKUP
You can also insert a date straight into the function. See the example below:
=VLOOKUP(DATEVALUE("02-Dec-2022"),A1:B13,2,0)
Points to Note
- Excel stores date as a number, and the date you are looking up and dates in the data need to be valid according to Excel’s date and time code.
- If you want to insert a date within the function, you need to use the DATEVALUE function to convert the inserted date into a valid Excel date.
Written by
Puneet Gogia
Microsoft MVP — Excel · Founder, Excel Champs
Twelve years of teaching Excel, and a decade before that using it as a data analyst. Every tutorial here is written from a file I actually built.