Md. Shamim Reza
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 lookup 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.
Download Practice Workbook
You can download the practice workbook from the download button below.
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.
Read More: How to Use VLOOKUP to Find a Value That Falls Between a Range
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 date format.
Read More: Lookup Value in a Range and Return in Excel (5 Easy Ways)
Things to Remember
- You must enter dates in the same format as the dates are 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. You may visit our ExcelDemy blog to learn more about excel.
