How to vlookup and return date format instead of number in Excel?
The vlookup function is used frequently in Excel for daily work. For example, you are trying to find a date based on the max value in a specified column with the Vlookup formula =VLOOKUP(MAX(C2:C8), C2:D8, 2, FALSE). However, you may notice that the date is displayed as a serial number instead of date format as below screenshot showed. Except for manually changing the cell formatting to date format, is there any handy way to handle it? This article will show you an easy way to solve this problem.
Vlookup and return date format instead of a number in Excel
Vlookup and return date format instead of a number in Excel
If you want to get the date based on the max value in a certain column with the Vlookup function, and keep the date format in the destination cell. Please do as follows.
1. Please enter this formula into a cell and press Enter key to get the correct result. See screenshot:
=TEXT(VLOOKUP(MAX(C2:C8), C2:D8, 2, FALSE),"MM/DD/YY")
Note: Please enclose the vlookup formula with TEXT() function. And don’t forget to specify the date format with double quotes at the end of the formula.
Related articles:
- How to copy source formatting of the lookup cell when using Vlookup in Excel?
- How to vlookup and return background color along with the lookup value in Excel?
- How to use vlookup and sum in Excel?
- How to vlookup return value in adjacent or next cell in Excel?
- How to vlookup value and return true or false / yes or no in Excel?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!