Vlookup Date Picker in Excel (Dynamic)

Add to Favorites
Author: | Edits: don

Make a dynamically updating vlookup date picker for excel that allows you to choose a date from three separate, but linked, drop down lists within the worksheet.

The days of each month will be accurate based on the year and month selected, even for February and leap years!

There will be 3 drop downs:

  1. Year
  2. Month
  3. Day

And each drop down list will update based on the current year and the month selection.

This tutorial combines: Vlookups, data validation, and complex custom formulas.

Part 1 - The Vlookup

This part shows you how to build the vlookups that use a date as a lookup value.

Note: DATEVALUE() could have been used to create the entire date for the vlookup in this video, like was done in the next video, but now you at least know multiple ways to do the same thing.

Part 2 - Dynamic Date Generation

This part shows you how to create the dates for the drop down menus and how to make them dynamic so that they always show the correct number of days based on the year and month selection.

Additional Resources

Vlookups (msft)

Vlookup to Return All Matching Results

Index/Match Lookups

Data Validation in Excel

3 Ways to Automatically Update Drop Down Lists in Excel

Dates & Times in Excel (msft)


Downloadable Files: Excel File

Question? Ask it in our Excel Forum


Our Excel Courses

  • Skill Level: All Levels
  • |
  • 5 hours+

How to make a fully featured professional form in Excel that is unbreakable. This includes how to use the form to store, view, edit, and delete data from a data storage worksheet.

  • Skill Level: All Levels
  • |
  • 2 hours

Send Emails from Excel using VBA and Macros. This course starts from the Basics and builds up to more advanced examples with attaching workbooks, worksheets, PDF's, automatically sending emails, including a signature, error handling, increasing speed, and more.

Similar Content on TeachExcel
Vlookup Partial Match in Excel
Tutorial: Return Vlookup results on partial matches of a cell's contents. You could type the start,...
Partial Match Lookup with Numbers in Excel
Tutorial: Perform lookups on numbers with partial matches. For instance, find the first number that...
Print the Current Date or Time in Excel
Tutorial: How to print the current date and or time on an Excel spreadsheet. This is a simple techni...
Wildcards in Excel
Tutorial: Wildcards are characters that allow you to make more robust functions, searches, and filt...
Format Cells in The Long Date Number Format in Excel
Macro: This free Excel macro formats a selection of cells in the Long Date number format in Excel...
Vlookup on Dates and Times in Excel
Tutorial: Perform a lookup on dates and times in Excel: vlookup, hlookup, index/match, any kind of l...