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:

`=VLOOKUP(E5,B5:C10,2,FALSE)`

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:

`VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])`

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.

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:

`=MAX(C5:C10)`

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:

`=VLOOKUP(E5,B5:C10,1,FALSE)`

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:

`=MAX(B5:B10)`

8. The new dataset will look as follows.

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

`=(VLOOKUP(E5,B5:C10,2,FALSE)`

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.

`=TEXT(VLOOKUP(E5,B5:C10,2,FALSE),"D-MMM-YY")`

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.

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

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

Get FREE Advanced Excel Exercises with Solutions!
Md. Shamim Reza

Md. Shamim Reza, a marine engineer with expertise in Excel and a fervent interest in VBA programming, sees programming as a time-saving tool for data manipulation, file handling, and internet interaction. His diverse skill set encompasses Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning. He holds a B.Sc in Naval Architecture & Marine Engineering from BUET and has transitioned into a content developer role, generating technical content focused on Excel and VBA. Beyond his professional pursuits,... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF