How to Apply VLOOKUP by Date in Excel

We use VLOOKUP by date to find dates in a data table. It is very useful to find the values corresponding to particular dates from a dataset. The value we look for in using the VLOOKUP function is called the lookup value. Therefore, the date is the lookup value when we apply VLOOKUP by date. This article shows how to apply VLOOKUP by date in the simplest way. The following picture gives an idea of how VLOOKUP by date works.

Apply VLOOKUP by Date in Excel

We are going to use the following dataset to illustrate how to apply VLOOKUP by date in Excel. The dataset contains the sales amounts corresponding to different dates.

Assume, you want to enter the date in cell E5 to find the corresponding amount of sales in cell F5.

You can easily do that by following the steps below.

Steps

1. First, enter the following formula in cell F5:

2. Don’t worry about the #N/A error.

3. Now, enter a date in cell E5 to get the desired sales amount as follows.

4. Be cautious about the date format while entering a date. You must enter the date in the same format as in the dataset which is MM/DD/YYYY in this case.

5. If the date you are looking for doesn’t exist in the dataset, it will show a #N/A error. For example, enter 2/4/2022 instead of 2/4/2022 and you will see the following.

How does the Formula Work?

1. The VLOOKUP function consists of the following arguments:

2. First, it asks for the value to lookup(lookup_value). Then the range of data to look up(table_array). After that, it asks the column number(col_num_index) where the return value exists in that range. Finally, the range_lookup argument asks whether you want to look for an Approximate Match(TRUE) or an Exact Match(FALSE).

3. If we compare this with the formula entered in cell F5 then we get,

• Lookup_value = E5 which contains the date we look for.
• Table_array = B5:C10 which contains the dataset.
• Col_index_num = 2 which is the second column of the dataset containing the sales amounts we wanted to find out.
• Range_lookup = FALSE, meaning looking for an exact match.

Read More: How to Use VLOOKUP Function with Exact Match in Excel

Perform VLOOKUP to Return Date in Excel

Now, what if we want to find the date corresponding to the maximum sales amount? Let’s follow the steps below for that.

Steps

1. First, enter the following formula in cell E5:

2. Then the MAX function in this formula will return the maximum sales amount from the Sales column.

3. After that, enter the following formula in cell F5:

4. But this gives the #N/A error.

5. It is because the value we are looking for remains in the second column in the table array.

6. To fix this problem, let’s interchange the Date and the Sales columns.

7. Then change the formula in cell E5 to the following one:

8. The new dataset will look as follows.

9. After that, apply the following formula in cell F5:

10. This will return the date as a number.

11. The simplest way to fix this problem is by changing the date format of that cell from the Home tab.

12. An alternative way to do that is by applying the following formula instead of the earlier one in cell F5.

13. The TEXT function in this formula will change the value obtained from the VLOOKUP formula to the date format.

Read More: How to Use VLOOKUP to Search Text in Excel

Things to Remember

• You must enter dates in the same format as the dates in the dataset.
• The lookup value must be in a column earlier than the column which contains the return value.
• Make sure the text format is inside double quotes (“”) in the TEXT function.

Download Practice Workbook

You can download the practice workbook from the download button below.

Conclusion

Now you know how to apply VLOOKUP by date in Excel. Please use the comment section below for further queries or suggestions.

Related Articles

• How to Use VLOOKUP with Two Lookup Values in Excel
• How to Apply Double VLOOKUP in Excel?
• How to Use VLOOKUP to Find Duplicates in Two Columns
• How to Find Second Match with VLOOKUP in Excel
• VLOOKUP and Return All Matches in Excel
• VLOOKUP Fuzzy Match in Excel
• Excel VLOOKUP to Find Last Value in Column
• Return the Highest Value Using VLOOKUP Function in Excel
• VLOOKUP with Numbers in Excel

<< Go Back to VLOOKUP a Range | Excel VLOOKUP Function | Excel Functions | Learn Excel