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


Excel VBA Course

(35% Sale Ends Jan. 26)

Excel VBA Course - From Beginner to Expert

200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course (35% Discount)

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...
Tutorial Details
Downloadable Files: Excel File
Excel VBA Course

(35% Sale Ends Jan. 26)

Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course (35% Discount)