Excel: How to Use VLOOKUP to Return Date Format


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:

Excel VLOOKUP return date format

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.

Additional Resources

The following tutorials explain how to perform other common operations in Excel:

Excel: Use VLOOKUP to Return Multiple Values Horizontally
Excel: Use VLOOKUP to Return Blank Instead of Zero
Excel: How to Use VLOOKUP by Exact Date

Leave a Reply

Your email address will not be published. Required fields are marked *