Table of Contents
VLOOKUP is a function in Excel that allows users to search for a specific value in a table and return a corresponding value from the same row. This function can also be used to retrieve data in a specific format, such as a date. Users can specify the format they want the date to appear in by using the TEXT function within the VLOOKUP formula. This allows for easier organization and manipulation of data in Excel spreadsheets.
Often when you use the VLOOKUP function in Excel to look up a specific value and return a corresponding date, the date will be shown as a number instead of a date.
You can force the VLOOKUP to return the date in a specific date format by using the TEXT function as follows:
=TEXT(VLOOKUP(D2, A2:B13, 2), "M/D/YYYY")
This particular formula looks up the value in cell D2 in the first column of the cell range A2:B13 and then returns the corresponding date from the second column in the range, formatted using a M/D/YYYY format.
The following example shows how to use this formula in practice.
Example: How to Use VLOOKUP to Return Date Format in Excel
Suppose we have the following dataset in Excel that shows when various employees started working at some company:
Suppose that we would like to look up the employee name “Andy” and return the date when he first started working at the company.
Suppose we attempt to use the following formula to do so:
=VLOOKUP(D2, A2:B13, 2)
The following screenshot shows how to use this formula in practice:
The VLOOKUP function is able to successfully look up “Andy” and return his start date, but the date is formatted as a number by default.
To force Excel to display this number as a date, we can use the following formula instead:
=TEXT(VLOOKUP(D2, A2:B13, 2), "M/D/YYYY")
The following screenshot shows how to use this formula in practice:
Notice that the formula returns 1/1/2024, which is the start date for Andy formatted as a date.
Also note that you can use the TEXT function to specify any date format that you would like.
For example, you could use the following formula to specify a YYYY-MM-DD format instead:
=TEXT(VLOOKUP(D2, A2:B13, 2), "YYYY-MM-DD")
The following screenshot shows how to use this formula in practice:
Notice that the formula returns 2024-01-01, which is the start date for Andy formatted using the date format that we specified.
The following tutorials explain how to perform other common operations in Excel: